[resolved] tAggregateRow to sum multiple columns

One Star

[resolved] tAggregateRow to sum multiple columns

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.

Accepted Solutions
One Star

Re: [resolved] tAggregateRow to sum multiple columns

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)

All Replies
One Star

Re: [resolved] tAggregateRow to sum multiple columns

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)
One Star

Re: [resolved] tAggregateRow to sum multiple columns

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

Re: [resolved] tAggregateRow to sum multiple columns

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads