Replacing simple IF condition with Set Analysis

[#1]

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

[#2]

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]

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

[#4]

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.

[jagan]

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.

[#6 · Accepted solution]

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:

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

B:

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.