I have not been able to create a working logic to perform this calculation. To illustrate, here is an inline table as a simplified version of the problem:
LOAD * Inline [
VALUE, ID, ID_LEVEL, MAIN_ID
15 444, 5562, 0
5 584, 132994, 1, 5562
2 172, 5564, 1, 5562
2 136, 171655, 1, 5562
935, 260763, 1, 5562
337, 5563, 1, 5562
1 880, 139198, 2, 132994
So this is a "hierarchical" fact table and these VALUEs are in a relationship with each other based on the ID_LEVEL. The actual VALUE of the ID=5562 (ID_level=0) is 15444 - 5585 - 2172- 2136 - 935 - 337 = 4280, in other words,ID_LEVEL=0 - ID_LEVEL 1, where MAIN_ID equals ID. And this logic goes on so that the actual VALUE of ID=132994 (ID_LEVEL=1 with a sub_ID) is ID_LEVEL=1 - ID_LEVEL=2 where MAIN_ID equals ID.
So I would need a new column: ACTUAL_VALUE.
I have ~1 million rows like this, so the logic should not be too heavy to calculate and that's why I'm reluctant to try any If() methods in the front-end. The back-end load can take resources though, so it is okay to be heavy there. Any insights? Is it possible in script editor or should I try to work it out with set expressions? It is okay to add columns if needed.
English is not my main language so feel free to ask if I failed to explain it in a reasonable fashion.
Thank you in advance!