How to calculate running totals (cumulative) based on a col?

bond88 · Post by **bond88** » Fri May 24, 2013 12:20 pm

Hi,
I am working on a task to calculate cumulative (running totals) of a column based on another column (code).

Example:

s.no code amount

1 10 100
2 20 150
3 10 50
4 10 150
5 20 30

Output

s.no code amount cumulative

1 10 100 100
2 20 150 160
3 10 50 150 (100+50)
4 10 150 300 (150+150)
5 20 30 190 (160+30)

I want to apply this logic on some million rows.

Please suggest me how to proceed on this.

Thank you,

ray.wurlod · Post by **ray.wurlod** » Fri May 24, 2013 4:14 pm

Use stage variable to remember the earlier row and to generate the running total. Make it the same data type as the output column, which must be compatible with the data type of Amount. Initialize the stage variable to 0.

Code: Select all

svCumulative = svCumulative + svprevAmount

jerome_rajan · Post by **jerome_rajan** » Sat May 25, 2013 12:01 pm

If your source is a database, I suggest you push this processing to the database. SQL OLAP functions are very efficient will provided optimal performance for the stated requirement unless of course you WANT to implement it using Datastage only.

bond88 · Post by **bond88** » Tue May 28, 2013 9:52 am

ray.wurlod wrote:Use stage variable to remember the earlier row and to generate the running total. Make it the same data type as the output column, which must be compatible with the data type of Amount. Initialize t ...

Thanks Ray,
I already tried that I am getting 0's and 1's that I didn't understand. It was working fine to calculate running total of a single column with out any conditions. But in this case I want calculate running totals based on CODE (It has to calculate running totals of each CODE individually). Please take a look at the following logic and correct me if I am wrong.

Stage variable's:

TEMP -

if (DSLink21.CATEGORY='10')
Then TEMP = TEMP1+ DSLink21.BUDGET_AMOUNT
Else If (DSLink21.CATEGORY='20')
Then TEMP = TEMP2+ DSLink21.BUDGET_AMOUNT
Else TEMP = TEMP3+ DSLink21.BUDGET_AMOUNT

TEMP1-

If (DSLink21.CATEGORY='10') Then TEMP2=TEMP else TEMP22=0

TEMP2-

If (DSLink21.CATEGORY='20') Then TEMP2=TEMP else TEMP22=0

TEMP3-

If (DSLink21.CATEGORY='30') Then TEMP2=TEMP else TEMP22=0

TEMP22-

All stage variables declared as decimal and initialize as 0 and Category is varchar thats why I used quotes '' inside if condition. TEMP1, TEMP2, TEMP3 has to store the running totals of corresponding codes 10,20 and 30. TEMP variable assigned to output column "Cumulative".

ArndW · Post by **ArndW** » Tue May 28, 2013 10:02 am

Is it certain that you only have '10', '20' and 'other' as values for CATEGORY? If so, I'd do 3 stage variables, each initialized to 0:
Cat10 derivation IF DSLink21.CATEGORY='10' THEN Cat10+DSLink21.BUDGET_AMOUNT ELSE Cat10
Cat20 derivation IF DSLink21.CATEGORY='20' THEN Cat20+DSLink21.BUDGET_AMOUNT ELSE Cat20
CatNN derivation IF DSLink21.CATEGORY#'10' AND DSLink21.CATEGORY#'20' THEN CatNN+DSLink21.BUDGET_AMOUNT ELSE CatNN

That takes care of your running totals for the 3 categories. Now define the derivation of your Cumulative column as

IF DSLink21.CATEGORY='10' THEN Cat10 ELSE IF DSLink21.CATEGORY='20' THEN Cat20 ELSE CatNN

srinivas.g · Post by **srinivas.g** » Tue May 28, 2013 11:44 am

You can use LastRowInGroup(%inputcol%) transformer fucntion. Input column is code.

bond88 · Post by **bond88** » Tue May 28, 2013 3:23 pm

Hi ArndW,
I am getting all zero's in cumulative column. Could you please guide me?

Thank you,

Regards,
Lohith Sama.

ray.wurlod · Post by **ray.wurlod** » Tue May 28, 2013 3:30 pm

Please post YOUR stage variable derivations.

In particular derivation expressions must NOT contain assignment statements.

bond88 · Post by **bond88** » Wed May 29, 2013 11:08 am

Stage Variables are like this:

TEMP1 - IF DSLink21.CAT='10' THEN TEMP1+DSLink21.BUDGET_AMOUNT ELSE TEMP1
TEMP2 - IF DSLink21.CAT='20' THEN TEMP2+DSLink21.BUDGET_AMOUNT ELSE TEMP2

..................

TEMP - if (DSLink21.CATEGORY='10')
Then TEMP1
Else If (DSLink21.CATEGORY='20')
Then TEMP2
Else TEMP3

I have one more doubt if I want to sort the data by multiple columns (after one by one) can I use sort stage or can I use the order by clause in the sql query itself?

chulett · Post by **chulett** » Wed May 29, 2013 11:30 am

bond88 wrote:I want to sort the data by multiple columns (after one by one) can I use sort stage or can I use the order by clause in the sql query itself?

Yes.

Either one can be used but for the latter you may still need to add a Sort stage set to "don't sort already sorted" or whatever it says nowadays so it doesn't re-sort it for you.

ArndW · Post by **ArndW** » Thu May 30, 2013 1:10 am

I don't see a derivation for TEMP3.

bond88 · Post by **bond88** » Thu May 30, 2013 9:53 am

Sorry ArndW,
I missed it in that post.

Thank you,

bond88 · Post by **bond88** » Mon Jun 03, 2013 9:16 am

There is a small problem. Finally summation (Running total) is working but it's not calculating in the order. I want to calculate running totals based on the month (accounting period column) 1 to 12. I am sorting at the source query and I tried sorting using sort stage also but it is calculating in random order and not in ascending or descending. Any suggestions please.

Thanks,

chulett · Post by **chulett** » Mon Jun 03, 2013 9:40 am

How many nodes is the job running on and how are you partitioning the data?

bond88 · Post by **bond88** » Mon Jun 03, 2013 9:42 am

Hi Chulett,
I am running it in sequential mode. I didn't enable partition reads and I selected sequential mode under transformer stage also.

Thanks,