One Star

How to implement running average logic in talend open studio

How to implement running average logic in talend open studio
Like e.g
Cloumn A   ColumnB
1                   (1+2+3)/3 = 2
2                    (2+3+4)/3 = 3
3                         ...
4
5
6
8 REPLIES
Moderator

Re: How to implement running average logic in talend open studio

Hi,
Have you already checked component TalendHelpCenter:tAggregateRow which receives a flow and aggregates it based on one or more columns. For each output line, are provided the aggregation key and the relevant result of set operations (min, max, sum...).
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to implement running average logic in talend open studio

Hi,
Thanks for the prompt reply. But my intention is not just to calculate the average but I would like to calculate the average for running values.
In the below example mentioned, I would like to calculate the average of 4 numbers. Here the input values for calculating the average always changes. Please reffer to the calculation column in the below table.
If you see the calculation, as the records move further the starting value also increases the row_ID by 1 and considers the next 4 values and calculates the average.
 
INPUTOUTPUTCALCULATION
14(A2+A3+A4+A5)/4
36(A3+A4+A5+A6)/4
58(A4+A5+A6+A7)/4
710(A5+A6+A7+A8)/4
912(A6+A7+A8+A9)/4
1114(A7+A8+A9+A10)/4
1316(A8+A9+A10+A11)/4
1518(A9+A10+A11+A12)/4
1720(A10+A11+A12+A13)/4
1922(A11+A12+A13+A14)/4
2124(A12+A13+A14+A15)/4
2326(A13+A14+A15+A16)/4
2520.25 
2714 
297.25 

Kindly drop me a note if you need any further information on the same.
Thank you..!
Regards,
Harinath
One Star

Re: How to implement running average logic in talend open studio

Hi,
Thanks for the prompt reply. But my intention is not just to calculate the average but I would like to calculate the average for running values.
In the below example mentioned, I would like to calculate the average of 4 numbers. Here the input values for calculating the average always changes. Please reffer to the calculation column in the below table.
If you see the calculation, as the records move further the starting value also increases the row_ID by 1 and considers the next 4 values and calculates the average.
 
INPUTOUTPUTCALCULATION
14(A2+A3+A4+A5)/4
36(A3+A4+A5+A6)/4
58(A4+A5+A6+A7)/4
710(A5+A6+A7+A8)/4
912(A6+A7+A8+A9)/4
1114(A7+A8+A9+A10)/4
1316(A8+A9+A10+A11)/4
1518(A9+A10+A11+A12)/4
1720(A10+A11+A12+A13)/4
1922(A11+A12+A13+A14)/4
2124(A12+A13+A14+A15)/4
2326(A13+A14+A15+A16)/4
2520.25 
2714 
297.25 

Kindly drop me a note if you need any further information on the same.
Thank you..!
Regards,
Harinath

INPUT    OUTPUT    CALCULATION
1          4        (A2+A3+A4+A5)/4
3           6        (A3+A4+A5+A6)/4
5           8        (A4+A5+A6+A7)/4
7          10        (A5+A6+A7+A8)/4
9          12        (A6+A7+A8+A9)/4
11        14        (A7+A8+A9+A10)/4
13        16        (A8+A9+A10+A11)/4
15        18        (A9+A10+A11+A12)/4
17        20        (A10+A11+A12+A13)/4
19        22        (A11+A12+A13+A14)/4
21        24        (A12+A13+A14+A15)/4
23        26        (A13+A14+A15+A16)/4
25        20.25   
27        14   
29        7.25   
Fifteen Stars

Re: How to implement running average logic in talend open studio

This can be done with a tMap and a a few tMap variables. This mini tutorial should give you an idea of how to do this. This isn't an example of exactly what you want to do, but it will show you a method to store the previous 3 values in order to calculate this with the current value. This example only caters for the last row value though, so you will need to extend it for this.
Rilhia Solutions
One Star

Re: How to implement running average logic in talend open studio

Hi Rhall,
I have tried the approach what ever you have provided and it is not working..
Can you please give me some other soultions on this logic..
Thanks,
Harinath
Fifteen Stars

Re: How to implement running average logic in talend open studio

This solution will work, but you have to be accurate with it. Below is a brief example of how I think you should do it.
Essentially you will need  5 tMap variables; val1, val2, val3, val4, average. They must be in that order from top to bottom. The value expressions will be (ignore the text in bold, that is just identifying the variable the value expression applies to)....
val1 = Var.val2
val2 = Var.val3
val3 = Var.val4
val4 = row1.value 
average = Var.val1!=null ? (Var.val1+Var.val2+Var.val3+Var.val4)/4 : 0 
Here is a screenshot of a tMap I have quickly set up to demonstrate this...

Keep in mind that for the first 3 values a 0 will be output as the average. This is because there are not 4 values to divide by 4 until the fourth value. This can be changed to whatever logic you want, I just chose this arbitrarily.
Rilhia Solutions
One Star

Re: How to implement running average logic in talend open studio

Hi,
I have tried the approach mentioned above and it is not working..
If possible can you pls provide other solution for running average logic
Fifteen Stars

Re: How to implement running average logic in talend open studio

Can you tell me what is not working and provide some screen shots to show what you have tried? 
Rilhia Solutions