Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Replacing simple IF condition with Set Analysis

I have 2 tables both are not connected.

Table 1

F1F2
A1
B2
C3
D4

Table 2

F3F4
A5
B6
C7
D8

I Created a straigt table with the dimention F3 and expression Sum( If(F3=F1,F2)). I get the following output.

Output

F3F2
A1
B2
C3
D4

How do I replace this IF condition with a set analysis, Please advice

1 Solution

Accepted Solutions
Not applicable

Re: Replacing simple IF condition with Set Analysis

Thanks all for the answers, I was looking for only front end resolution, because both my tables are at different levels. Following is the resolution which worked for me.

A:

F1F2A_FAA_FBA_FCA_FD
A11
B21
C31
D41

B:

F3F4B_FAB_FBB_FCB_FD
A51
B61
C71
D81

Front end :

Dimention = F3

Actual Exp = Sum( If(F3=F1,F2))

Expression = Alt( 

                           Sum({<A_FA={1},B_FA=>} F2),

                           Sum({<A_FB={1},B_FB=>} F2),

                           Sum({<A_FC={1},B_FC=>} F2),

                           Sum({<A_FD={1},B_FD=>} F2)

                         )

Though the expression looks big and complicated, it is 100 times faster then the actual expression.

5 Replies
Not applicable

Re: Replacing simple IF condition with Set Analysis

I have a very complicated datamodel, There are reasons i cannot connect them in the data model.

This If condition gives me correct results but it takes 100 times the memory of the file to calculate. If I remove the If condition the results are quick but wrong.

Set Analysis allows us to calculate very complex logic, but there is no alternate for this simple logic.

somenathroy
Contributor III

Re: Replacing simple IF condition with Set Analysis

Hi,

You can fetch F2 value of having F1=F3 in script instead of expression. It will have faster user experience at UI.

The below code snipets can be used:

A:

LOAD * Inline

[F1,          F2

A,          1

B,          2

C,          3

D,          4];

B:

LOAD * Inline

[F3,          F4

A,          5

B,          6

C,          7

D,          8];

C:

LOAD *, Lookup('F2','F1',F3,'A') as F5 Resident B;

DROP Table B;

Regards,

Som

Not applicable

Re: Replacing simple IF condition with Set Analysis

It looks simple when I only have 2 tables. But my actual datamodel tables are at different levels. Only way to do this is by creating a heavy linktable. Currently I dont want to create a heavy linktable just for 1 expression in the front end.

I am just trying to get a solution in the frontend.

MVP & Luminary
MVP & Luminary

Re: Replacing simple IF condition with Set Analysis

Hi Abdul,

In your data model while loading table2 use mapping load to arrive a new column with F2 values, so that in chart you can straight get the values very quickly.

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Replacing simple IF condition with Set Analysis

Thanks all for the answers, I was looking for only front end resolution, because both my tables are at different levels. Following is the resolution which worked for me.

A:

F1F2A_FAA_FBA_FCA_FD
A11
B21
C31
D41

B:

F3F4B_FAB_FBB_FCB_FD
A51
B61
C71
D81

Front end :

Dimention = F3

Actual Exp = Sum( If(F3=F1,F2))

Expression = Alt( 

                           Sum({<A_FA={1},B_FA=>} F2),

                           Sum({<A_FB={1},B_FB=>} F2),

                           Sum({<A_FC={1},B_FC=>} F2),

                           Sum({<A_FD={1},B_FD=>} F2)

                         )

Though the expression looks big and complicated, it is 100 times faster then the actual expression.