How to map master table in existing Cross table

[Nabyendu Adhikari]

I have a cross table visualization in Spotfire Analyst 7.11as bellow;

 

 

 

COL-A

COL-B

COL-C

COL-D

 

 

 

 

 

D2

D4

D5

 

 

 

 

 

 

 

 

 

 

A1

B1

C1

1

2

7

 

 

 

B2

C2

1

3

1

 

 

 

B3

C3

 

4

 

 

 

A2

B2

C1

4

 

3

 

 

 

B4

C3

2

1

 

 

 

 

B5

C4

5

 

4

 

 

A3

B1

C3

8

10

2

 

 

 

B2

C1

 

3

 

 

 

 

B5

C4

1

5

17

 

 

 

 

 

Row count

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now COL-D values are calculates inside the Oracle view based on some criteria and possible values are D1, D2, D3,D4 and D5. When performing Oracle inner join, D2,D4 and D5 values are returned in Data Table, which is correct. However, I like to show D1 and D3 as well in this visualization with blank values. Outer join will not work as I am getting empty rows ( 2 in count) for D1 and D3, so total count is wrong.

I have tried creating new table with COL-D values as master table, but unable to map this with existing Data Table.

Anyone have any idea how to achieve this in Cross Table.

So final Cross table view will be;

 

 

 

COL-A

COL-B

COL-C

 

COL-D

 

 

 

 

 

D1

D2

D3

D4

D5

 

 

 

 

 

 

 

 

 

 

 

 

A1

B1

C1

 

1

 

2

7

 

 

 

B2

C2

 

1

 

3

1

 

 

 

B3

C3

 

 

 

4

 

 

 

A2

B2

C1

 

4

 

 

3

 

 

 

B4

C3

 

2

 

1

 

 

 

 

B5

C4

 

5

 

 

4

 

 

A3

B1

C3

 

8

 

10

2

 

 

 

B2

C1

 

 

 

3

 

 

 

 

B5

C4

 

1

 

5

17

 

 

 

 

 

 

Row count

 

 

 

Thanks in advance.