Calculations where certain conditions are met - script or set expression?

[SamuliPeura]

Hi all! 

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: 

Table:

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!

[maxgro]

You can try this in the script.
With a join I add to every row the sum of the values with ID_LEVEL -1 and with MAN_ID equal to the ID of the row.

LEFT JOIN (Table)
LOAD
ID_LEVEL -1 AS ID_LEVEL,
MAIN_ID AS ID,
SUM(VALUE) as VALUE_ID_LEVEL_MINUS_1
RESIDENT Table
group by ID_LEVEL, MAIN_ID;