topic Re: Replacing simple IF condition with Set Analysis in QlikView

Replacing simple IF condition with Set Analysis

Fri, 21 Jun 2013 16:02:01 GMT

I have 2 tables both are not connected.

Table 1

F1	F2
A	1
B	2
C	3
D	4

Table 2

F3	F4
A	5
B	6
C	7
D	8

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

Output

F3	F2
A	1
B	2
C	3
D	4

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

Re: Replacing simple IF condition with Set Analysis

Sat, 22 Jun 2013 03:02:49 GMT

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.

Re: Replacing simple IF condition with Set Analysis

somenathroy — Sat, 22 Jun 2013 05:03:10 GMT

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:

LOAD * Inline

[F1, F2

A, 1

B, 2

C, 3

D, 4];

LOAD * Inline

[F3, F4

A, 5

B, 6

C, 7

D, 8];

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

DROP Table B;

Regards,

Som

Re: Replacing simple IF condition with Set Analysis

Sat, 22 Jun 2013 05:15:28 GMT

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.

Re: Replacing simple IF condition with Set Analysis

jagan — Sat, 22 Jun 2013 08:04:44 GMT

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.

Re: Replacing simple IF condition with Set Analysis

Tue, 25 Jun 2013 00:55:46 GMT

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.

F1	F2	A_FA	A_FB	A_FC	A_FD
A	1	1
B	2		1
C	3			1
D	4				1

F3	F4	B_FA	B_FB	B_FC	B_FD
A	5	1
B	6		1
C	7			1
D	8				1

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.