Attached is the correct display, which is the correct way to show whether hosp a b ,c has a value or null .
hosp.qvw 142.5 K
Have look at the load script, also pasted in below. In essence :
a) In line load of your test data in table Data
b) Load distinct values of Prod into table Temp
c) Cartesian join of distinct values of Hosp into table Temp
- Table Temp now contains all combinations of Prod and Hosp
d) Outer join table Temp onto table Temp
- Which fills in the missing Prod and Hosp combinations giving them a null() value for Value
Then once you have generated this table creating the Straight table is dead easy as you have observed.
I am an advocate of doing everything one can in the script so as to make the dashboard objects as easy to create and simple as possible.
LOAD * INLINE [
Hosp, Prod, Value
a, prod1, 1
a, prod2, 2
a, prod3, 3
b, prod2, 4
b, prod3, 5
c, prod3, 11
c, prod3, 12
Outer Join ( Temp )
outer join ( Data )
drop table Temp ;