[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.