topic Re: [resolved] tAggregateRow to sum multiple columns in Talend Studio

[resolved] tAggregateRow to sum multiple columns

Anonymous — Mon, 14 May 2012 19:27:24 GMT

Hi,
I have file which has the data like this.
Order#;VAS1 Quantity;VAS1 Amount;VAS2 Quantity;VAS2 Amount;VAS3 Quantity;VAS3 Amount
1000; 1; 5.5; 1; 4.0; 0; 0
2000; 2; 11.0; 1; 4.0; 1; 5.0
1000; 1; 5.5; 2; 8.0; 1; 5.0
3000; 1; 5.5; 1; 4.0; 2; 10.0
3000; 0; 0.0 2; 8.0; 1; 5.0
I need the output as shown below. The second column is the sum of vas1quantity+vas2quantity+vas3quantity and the third column is the sum of vas1amount+vas2amount+vas3amount, grouped by order #. The input file has 2 records for order # 1000, so, the sum function should take the quantities and amount from both records.
Order #;VAS Quantity; VAS Amount
1000; 6; 28.0
2000; 4; 20.0
3000; 7; 32.5
Can some one help me to acheive the result? I know I can do it with a tFlowToIterate and tJavaFlex, but I prefer to use something like a tAggregateRow.
Thanks,
Balaji.

Re: [resolved] tAggregateRow to sum multiple columns

Anonymous — Mon, 14 May 2012 21:57:51 GMT

There are 2 steps
First, combine the fields in a tMap
send the input to a tMap.
on the output side of the tMap have 3 fields: order_num, vas_quantity, and vas_amount
set the order_num expression to: row1.order
set the vas_quantity expression to: row1.VAS1Quantity + row1.VAS2Quantity + row1.VAS3Quantity
set the vas_amount expression to: row1.VAS1Amount + row1.VAS2Amount + row1.VAS3Amount

Step 2, aggregate the output in a tAggregateRow
group by order_num
sum(vas_quantity)
sum(vas_amount)

Re: [resolved] tAggregateRow to sum multiple columns

Anonymous — Mon, 14 May 2012 22:18:50 GMT

Thanks, it worked. I should have thought about it, my bad.
Thanks,
Balaji.

Re: [resolved] tAggregateRow to sum multiple columns

Anonymous — Tue, 15 May 2012 22:43:55 GMT

Hi Balaji, glad it worked. We are all always learning.

Re: [resolved] tAggregateRow to sum multiple columns

Anonymous — Sun, 10 May 2020 02:11:50 GMT

Hi ,

actually I tried the same to sum up value from multiple colomns from my input excel file , I have 12 colomns (sales per months ) and I have to sum up to get the annual sales ,so I have added tmap and set :
row1.Jan_sales+row1.Feb_sales+...+row1.Dec_sales <==> Annual_sales
but it fails as it doesnt recognized colmns after row1.June_sales .
Any idea how I can overcome this to calculate the annual sales from my input excel ?

Thanks in advance