We have moved to www.dataGenX.net, Keep Learning with us.

Saturday, July 05, 2014

Count Rows and Calculate Sum in same Aggregator


Generally, we are using Fork n Join method ( divide the data into 2 links which goes to 2 Aggregator ) to calculate Sum and Count Rows of Data because Aggregator Stage is providing one type of aggregation at a time, You can Count the rows or can do some other aggregation.
            Here, I have describe a way in which we can Count Rows and Calculate Sum in a Single Aggregator Stage with some limitations :-)


Design :

As per design, we used Column Generator and Aggregator Stage to calculate and count.

Source :   

Source is a DB2 table ( EMPLOYEE) which is having 42 rows having First Name, Last Name, Job, Salary etc. We are simply reading the data from DB2 connector stage.

 

Column Generator Stage Properties :


In Column Generator Stage, we are generating a dummy column having value 1 each time.
       

By editing extended properties of "dummy" column we can make datastage to generate always 1 for each row.  Now, How you can edit Column Extended Properties

 
As in above Imgae, you can see the "dummy" column metadata. You need to double click on left most of column ( where is Column Number is written ) for which you want to  edit extended properties. Edit n do like below image -

Aggregator Stage Properties

Setup the Aggregate Stage Properties like below -
Grouping Keys -- dummy
Aggregation Type - Calculation
Calculate Sum on  - dummy and Salary
as both column is numeric you will get the output you wanted.

 
in Mapping tab, Map only calculated columns to output.


Row Count -- Sum of dummy column value which is 1 for each row so -- 1+1+1+.....
Total Salary -- Sum of Salary defined in each row.


OutPut :






Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you

https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098

No comments :

Post a Comment