Sum of n number of rows
Posted: Thu Mar 20, 2014 2:36 pm
Hello,
My requirement is, I get data from a table with approx 75,000 rows. Right now I need to separate them into chunks of 10 rows. I have 2 columns which must be summed for every 10 rows based on the sum I need to generate another row which makes the total sum of the previous 10 rows zero.
My approach,
I have used stage variables to divide them into chunks of rows with the help of @inrownum and @outrownum and some math functions. 1st row will be header and remaining 9 rows detail. I need to sum up all the rows until I get the next header.
After that I have used stage variables to calculate the sum of 10 rows, added new 2 columns to have their values present. Next step i have used used remove duplicates to remove all the records and retained the last record, so that I can funnel that record with the 10 records and makes it a chunk of 11 records.
e.g.;
1 row - header
2 row - detail
9 row -detail
10 row - header
transforms to
1 row - header
2 row - detail
9 row - detail
10 row - new offset records (which has 2 columns with values that will make the sum of previous nine rows + the new offset column value = 0 )
10 row - header
How can I do this in transformer itself instead of using remove duplicates adding additional columns for summation and making 2 copies of data
removing duplicates retaining the last value and funneling it out to the sequential file?
I am aligning it right now with a unique number for every 10 set of rows so that the chunk of rows remain.
My requirement is, I get data from a table with approx 75,000 rows. Right now I need to separate them into chunks of 10 rows. I have 2 columns which must be summed for every 10 rows based on the sum I need to generate another row which makes the total sum of the previous 10 rows zero.
My approach,
I have used stage variables to divide them into chunks of rows with the help of @inrownum and @outrownum and some math functions. 1st row will be header and remaining 9 rows detail. I need to sum up all the rows until I get the next header.
After that I have used stage variables to calculate the sum of 10 rows, added new 2 columns to have their values present. Next step i have used used remove duplicates to remove all the records and retained the last record, so that I can funnel that record with the 10 records and makes it a chunk of 11 records.
e.g.;
1 row - header
2 row - detail
9 row -detail
10 row - header
transforms to
1 row - header
2 row - detail
9 row - detail
10 row - new offset records (which has 2 columns with values that will make the sum of previous nine rows + the new offset column value = 0 )
10 row - header
How can I do this in transformer itself instead of using remove duplicates adding additional columns for summation and making 2 copies of data
removing duplicates retaining the last value and funneling it out to the sequential file?
I am aligning it right now with a unique number for every 10 set of rows so that the chunk of rows remain.