One Star

Group and Sum rows based on condition

Hi,
I've following dataset
ID|Name|Amount|Sum|Row
1|AAA|20| 10| 1
1|BBB|10| 30| 1
1|CCC|30| 70| 11 -----------> CurrentRow
1|DDD|20| 40| 1
1|EEE|20| 20| 1
I need to write a Talend Job which will calculate Sumfield based on CurrentRow # (in this case it is 11 in CCC row). All rows below CurrentRow should calculate Sum till last record for ID 1 (including that row). For example for row DDD, Sum = Amount for row DDD (20) + Amount for row EEE (20).
Similarly for row BBB Sum = Amount for BBB(10) + Amount for AAA(20).
Thank you very much in advance.
1 REPLY
Fifteen Stars

Re: Group and Sum rows based on condition

You can do this with a tMap. First (before the tMap) use a tSort to order you data correctly. Then in your tMap, create a tMap variable (the box between the "in" and "out" data). Let's call that "SUM". You want to use your Amount field to add to this value for every row as seen in the screenshot below.

This is a very basic set up and will not reset according to a group. However, you can introduce that logic by using the fact that tMap variables can store their values between rows. To see a tutorial which shows this (how to carry out previous row lookups), take a look here (https://www.rilhia.com/quicktips/quick-tip-compare-row-value-against-value-previous-row).
Rilhia Solutions