rolling or cumulative sum

[diogenes]

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!

[Anonymous · Accepted solution]

Hi,

You can do this using a tJavaFlex component:

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

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

Then add the code:

 

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

[lokeshbabu]

Hi All,

Need talned job for below Source .

Input Source:

Quater |  PPID | Values

14-Q1  | 1         |  10

14-Q2  |  1        | null

14-Q3  | 1         | null

14-Q4  |  1        | 20

Output :

Quater |  PPID | Values

14-Q1  | 1         |  10

14-Q2  |  1        |  10

14-Q3  | 1         | 10

14-Q4  |  1        | 20

for missing Quaters need to populate previous value .

Please give me solution.