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

How to implement running average logic in talend open studio

_AnonymousUser — Mon, 17 Aug 2015 08:01:44 GMT

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

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

Anonymous — Tue, 18 Aug 2015 03:51:25 GMT

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

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

_AnonymousUser — Tue, 18 Aug 2015 11:47:57 GMT

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.

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

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

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

_AnonymousUser — Tue, 18 Aug 2015 11:52:22 GMT

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.

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

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

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

Anonymous — Tue, 18 Aug 2015 12:05:09 GMT

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.

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

_AnonymousUser — Thu, 20 Aug 2015 08:52:51 GMT

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

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

Anonymous — Thu, 20 Aug 2015 09:22:15 GMT

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.

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

_AnonymousUser — Thu, 27 Aug 2015 11:23:14 GMT

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

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

Anonymous — Thu, 27 Aug 2015 11:32:36 GMT

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