Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In my data model, I have a many to one relationship between 2 tables which is causing an issue that whenever I do a sum, it doesn't return a correct value if I do expression total, however I get correct value if I do sum of rows.
But I need it in an expression for which I need to use aggr to achieve the result, i.e. aggr the sum on all the columns of table1 first and then doing the sum on the expression.
See attached code for better clarity of the issue.
But I'm facing performance issue in my application and was wondering if I can optimize the calculation anyhow either through data model fix or some expression not using aggr.
So far i'm not successful with it, if anyone can suggest alternate solution, it will be helpful.
Regards,
Hello,
you can edit your script to join both fact tables. Then sum(Value) will work as expected,
Fact1:
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
];
left join
LOAD * Inline
[Key, Val
1, 10
2, 20
];
regards
tim
Hello,
you can edit your script to join both fact tables. Then sum(Value) will work as expected,
Fact1:
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
];
left join
LOAD * Inline
[Key, Val
1, 10
2, 20
];
regards
tim
Not sure I follow your requirement or your problem, but if you aggregate over a field with 3 values (F2), you will be adding 3 virtual rows. which will double count the value 10. If you aggregate over F1, you will get the "correct" total of 30.
Hi,
pls check the my attachment
Regards
Hemanth
Hi,
The Key column should be UNIQUE values or make UNIQUE values column as Key. Then only you can get the correct values.
Hello,
If I understand your requirement, another solution is to use "applymach" function in edit script.
See below my solution:
Fact2:
Mapping
LOAD * Inline
[Key, Val
1, 10
2, 20
];
Fact1:
LOAD
ApplyMap('Fact2',Key,0) as Val,
*
;
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
];
What Tim has mentioned is correct, which I figured myself also in next couple of days. Although your solution is also correct Eliza, but I have more than two columns in the mapping table in actual app.
Hi Abeer,
It would not be no problem in this case (than two columns in the mapping table). See below the example:
Fact2:
Mapping
LOAD
Key,
Val1&'#'&Val2;
LOAD * Inline
[Key, Val1, Val2,
1, 10, 50
2, 20, 160
];
Fact1:
LOAD
subfield(ApplyMap('Fact2',Key,0),'#',1) as Val1,
subfield(ApplyMap('Fact2',Key,0),'#',2) as Val2,
*;
LOAD * Inline
[F1, F2, Key
A, B, 1
A, C, 1
A, D, 2
];
I prefer APPLYMAP function versus with JOIN or LEFT JON.