Comparing two row based on value

[RA6]

Hello guys,

I have an input file delimited:

What i need to do is to subtract the amount of the highest sequence with highest sequence - 1 based on a composition of columns which is ID, NAME, SURNAME:

For example:

And my expected output should be:

Is there a way i can achieve this guys?

Many thanks for your help.

Best regards,

Duke

[Anonymous]

@Rohit Aftab​ , use tMemorizeRow to cache the last amount value and use current amount to subtract last amount to the new amount for each row, finally, use a tAggregateRow to perform aggregation operation and get the row with the highest sequence for each group.

 

Hope it helps you!

Regards

Shong

[RA6]

Hello @Shicong Hong​ 

Many thanks for your response.

Can you attach me the example job please?

Thank you.

Best regards,

Duke

[Anonymous]

see attachment, the example job is export from Talend Enterprise Studio 7.3.1

[RA6]

@Shicong Hong​  Is this possible to achieve this one? This is when you have more than 2 combination.  

[Anonymous]

Yes, try this expression on tMap: (assuming the data type of amt field is int/integer)

amount_tMemorizeRows_1[1]==null?0:row2.amount-amount_tMemorizeRows_1[1]

and then, use tFilterRow after tMap to remove the rows that the amt value is 0.