Four Stars

rolling or cumulative sum

Hi,

I'm relatively new to Talend and looking for an way to create a rolling or cumulative sum over different groups. For example:

Input:

Groups|Value

A| 1

A| 0

A|1

B|2

B|0

C|3

 

Output:

Groups|Value|C_Sum

A| 1|1

A| 0|1

A|1|2

B|2|2

B|0|2

C|3|3

Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: rolling or cumulative sum

Hi,

 

You can do this using a tJavaFlex component:

 

RunningGroupTotals.png

 

I'm using a tFixedFlowInput to simulate the data, as per your example:

 

tFixedFlowInput.png

 

Add the C_Sum column to the schema of your tJavaFlex component:

 

tJavaFlex_Schema.png

 

Then add the code:

 

 

 tJavaFlex2.png

 

I've included the code here, so you can just copy and paste it into your job:

 

String  lastGroup = "!";
Integer total = 0;

 

if (!lastGroup.equals(row1.Groups)) {
total = 0;
lastGroup = row1.Groups;
}

total += row1.Value;

row2.C_Sum = total;

 

It's essential that the data is sorted by Groups, but from what you're looking to achieve, I'd expect this to be the case anyway.

 

Regards,

 

 

Chris

1 REPLY
Six Stars

Re: rolling or cumulative sum

Hi,

 

You can do this using a tJavaFlex component:

 

RunningGroupTotals.png

 

I'm using a tFixedFlowInput to simulate the data, as per your example:

 

tFixedFlowInput.png

 

Add the C_Sum column to the schema of your tJavaFlex component:

 

tJavaFlex_Schema.png

 

Then add the code:

 

 

 tJavaFlex2.png

 

I've included the code here, so you can just copy and paste it into your job:

 

String  lastGroup = "!";
Integer total = 0;

 

if (!lastGroup.equals(row1.Groups)) {
total = 0;
lastGroup = row1.Groups;
}

total += row1.Value;

row2.C_Sum = total;

 

It's essential that the data is sorted by Groups, but from what you're looking to achieve, I'd expect this to be the case anyway.

 

Regards,

 

 

Chris