Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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.
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.
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.