Island dimension in a table, expression avoiding an IF?!

[adamdavi3s]

Hi All,

Bit of a strange one this. We have an island dimension, which has the same values as some column names in the fact table.

The simple reason being the dataset is 65,000,000 rows and having this dimension as an additional column would potentially increase this by 20 times!!

Therefore under the advice of qliktech we created the island dimension, and renamed the amount columns with the same values.

Consider this as a basic example:

Island Table:

cost_type
BLOOD
DRUGS
WARDS AND NURSING
IMPLANTS

Fact Table:

Header 1	Header 2	Header 3	Header 4	Header 5
HRG	BLOOD	DRUGS	WARDS AND NURSING	IMPLANTS
a	100	20	40	23
b	200	10	50	456

On the whole this works very well for us, the only issue being when we want to create a table with this dimension, the only way we've found to add the expression is a large IF statement, for example:

=
if(cost_type='BLOOD',sum(BLOOD),
if(cost_type='DRUGS',sum(DRUGS),
if(cost_type='WARDS AND NURSING',sum([WARDS AND NURSING]),
IF(cost_type='IMPLANTS',SUM(IMPLANTS),0))))

But this is obviously an incredibly slow way for the system to calculate

Is there a simple way to include the dimension in the formula?

I was hoping something like this would work:

=sum('['&cost_type&']')

But it doesn't

[Gysbert_Wassenaar · Accepted solution]

You can try a pick-match combination:

=pick(match(cost_type,'BLOOD','DRUGS','WARDS AND NURSING','IMPLANTS'),

sum(BLOOD),

sum(DRUGS),

sum([WARDS AND NURSING]),

sum(IMPLANTS))

But the smart way is to use the CrossTable function in the script:

LOAD @1 as cost_type

FROM

comm89620.xls

(biff, no labels, table is cost_types$);

CrossTable(cost_type, value)

LOAD HRG,

     BLOOD,

     DRUGS,

     [WARDS AND NURSING],

     IMPLANTS

FROM

comm89620.xls

(biff, embedded labels, table is fact$);

You can then use cost_type as dimension and sum(value) as expression. See attached qvw

[adamdavi3s]

Sorry I didn't get my usual email to tell me this reply was here!

I'll try the pick match and see if it runs better than the IF statement.

The cross-table unfortunately isn't viable due to the volumes of data!

[adamdavi3s]

Pick match improved the speed 4 fold

10 seconds down to 2.5 on my larger data set.