calculate value for last record per group

[pepe2209]

Hello

Please look at the example below, i would like to create in the script the field 'Remaining':

ID	Cycle	Value	Endvalue	Remaining
1	1	8	15	0
1	2	11	15	0
1	3	13	15	2
2	1	8	20	0
2	2	11	20	0
2	3	13	20	0
2	4	16	20	4

So, every time the max cycle of an ID is reached I want to calculate the remaining difference between 'Endvalue' and 'Value'. But for the other cycles 'Remaining' should be zero. So it's an calculation per group (ID).

Your help would be much appreciated,

Regards,

Peter

[#2 · Accepted solution]

Peter,

I would do sth like that. I create a list of the last value of cycle (use of Max but also we could use LastValue) concatenated with the ID. So that I get a list of ID_LastValue.
Afterwards, just a test to see if we compute the remaining or not.

Temp:
LOAD ID,
Max(Cycle) as LC
FROM [108376.xlsx](ooxml, embedded labels, table is Feuil1)
GROUP BY ID;

Left Join(Temp)
LOAD *, ID & '_' & LC as IDLC

Resident Temp;

Data:;
LOAD ID,
Cycle,
Value,
EndValue,
if (exists(IDLC, ID & '_' & Cycle), EndValue-Value, 0 ) as Remaining

FROM [108376.xlsx](ooxml, embedded labels, table is Feuil1);

drop table Temp;

Fabrice

[pepe2209]

Ok thanks!