How to make this IF statement for Calculated Dimension efficient in the script?

[jblomqvist]

Hi,

I have an IF statement for a calculated dimension:

=IF(SourceOrigin = 'Warehouse' or SourceOrigin = 'Store', CurrencyA,

If(SourceOrigin = 'Online', CurrencyB))

How can I write this efficiently in the script?

CurrencyA, and Currency B are in seperate tables so I cannot copy the IF statement back to the script.

[balabhaskarqlik]

May be this:

=Pick(Match(SourceOrigin,'Warehouse','Store'), CurrencyA, CurrencyB)

[vamsee]

Are both tables related based on the column SourceOrigin?

Is it possible for you to share a screenshot of your data model

[jblomqvist]

Hi,

The two tables are not related based on the column SourceOrigin.

There is no direct relationship of the two tables.

The two tables are linked via a middle man table.

I wrote the IF statement like this in the script where SourceOrigin resides:

=IF(SourceOrigin = 'Warehouse' or SourceOrigin = 'Store', 1,

If(SourceOrigin = 'Online', 0)) as FIeldFlag

And then in the UI for a Calculated Dimension an IF statement that is

IF(FieldFlag = 1, CurrencyA, If(FieldFlag = 0, CurrencyB))

Not sure if this is more efficient but this is what I can think of.

[dwforest]

You could do this in script by selecting the data from the RESIDENT tables and append the field to maybe your link table.

You would use your original IF after selecting/joining from the already loaded tables.

[Temp]:

LOAD Key, CurrencyA

RESIDENT Table1;

JOIN

LOAD Key, SourceOrigin

RESIDENT LinkTable;

JOIN

LOAD Key, CurrencyB

RESIDENT Table2;

[final]:

Load *,

IF(SourceOrigin = 'Warehouse' or SourceOrigin = 'Store', CurrencyA,

If(SourceOrigin = 'Online', CurrencyB))

RESIDENT Temp;

Drop table Temp;