topic Re: How to make this IF statement for Calculated Dimension efficient in the script? in App Development

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

jblomqvist — Tue, 21 Aug 2018 14:43:45 GMT

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.

BalaBhaskar_Qlik — Tue, 21 Aug 2018 15:06:17 GMT

May be this:

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

vamsee — Tue, 21 Aug 2018 15:12:54 GMT

Are both tables related based on the column SourceOrigin?

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

jblomqvist — Tue, 21 Aug 2018 15:45:19 GMT

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 — Tue, 21 Aug 2018 18:16:53 GMT

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;