# 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

Tags (4)
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.

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

## 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.