Cumulative Sum's

[Raamc]

Hi,

I am trying to calculate the cumulative sum of amounts for an account in my job.

I have a file with the sample data as below
ACCNO AMOUNT
================
123 12
123 13
123 03
234 5
234 5
234 6
In the target file i want o/p to be
ACCNO AMOUNT
====================
123 12
123 25
123 28
234 5
234 10
234 16

Please suggest me how to calculate these sums?

[ray.wurlod]

Two Aggregator stages.

Code: Select all

Detail ---->  Copy1  -----------------------------> Join2 ---->  Target
                |                                     ^
                |                                     |
                +--> Copy2  ----->  Aggr1 ------->  Join1
                        |                             ^
                        |                             |
                        +-------->  Aggr2  -----------+

[ShaneMuir]

At a glace the only thing I can think of is to use a transform with stage variables that checks the key value and adds the values together, whilst the key value remains the same.

You would require 1 stage variable to hold the previous rows key value, one to hold/compare that to the current row's key value, one to add the values, and one to reset the total to zero if the key value changes. You then set your output derivation to be the value of the stage variable holding the cumulative total for that row.

Remember to hash partition and sort your input stream for this to work.

[Raamc]

I designed my job with the Keychange column property in sort stage and staging variables in the transformer stage as below.
with the Key Change column property in the before sort stage i am capturing whether the key is changing or remaining same or not.
And in the staging variable in transformer, if Keychange=0 then Stagevar+Amount else pass the amount as it is.

Drag this staging variable amount field into CUMULATIVE AMOUNT COLUMN in the final o/p.