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

SamuliPeura — Mon, 02 Jan 2023 11:27:52 GMT

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!

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

maxgro — Mon, 02 Jan 2023 12:11:34 GMT

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;

topic Re: Calculations where certain conditions are met - script or set expression? in QlikView

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

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