aggregator

dspxlearn · Post by **dspxlearn** » Fri Nov 11, 2005 5:53 am

Hi,

In my job i have to count the no. of records coming from the input and i have to carry that to the transformer for some formatting.Along with that count , i have to carry one column from the source to the same transformer. For this i am using an aggregator to count the total rows and keeping that in the group. I have to pass one more column simply.Since, i am using an aggregator i can count the records, but i cannot pass the column through it.But to do this i need to keep that column also in the grouping list..
Is there any way around to this...........

Thanks,
dspxlearn

Kirtikumar · Post by **Kirtikumar** » Fri Nov 11, 2005 6:46 am

If I got it correctly and what you intend to do is as follows:

there are 3 columns: A, B, C and you want to group on A & B and get count on A & B.
and you also want column C along with the count...

You may need to duplicate the data on other link before aggregation and then do the join on the duplicated dat once the aggregation is done.

kumar_s · Post by **kumar_s** » Fri Nov 11, 2005 9:02 am

Hi,
Reconsidire your requirement once again.
You need to group based on a column an you need to carry out another column with grouping. Which means one column with fewer rows and another with full number of rows. How do you plan to match it.
Getting the first value for each group?
Getting last value for each group?
Getting average for each gropu?
Any of the above answer will give you the way to get the column out of the transformer.

regards
kumar

dspxlearn · Post by **dspxlearn** » Mon Nov 14, 2005 3:23 am

Hi kirtikumar and kumar_s,

My requirement is to count the records of one column by grouping them and to simply pass another column to the next transformer for further formatting. I have a transformer from where 2 columns are there say A & B.Next an aggregator is used to count the no of rows of column 'A' .So in the aggregator i gave column A as a Group by column and same for "Aggregation type" as "Count".So from my source 600 rows are coming and the count is passed.Whereas i also need to pass column 'B' along with it without doing any grouping or anykind of caluculation to the next transformer....
I guess it the column 'B' needs to be kept in the grouping for it to pass to the output of the aggregator...Please advice on this....
If there is any other method to do this please let me know............

kumar_s · Post by **kumar_s** » Mon Nov 14, 2005 4:41 am

Hi,

Can you write a SQL code for you requirement.
You may not. Aggregator works similar to you SQL code. You cannot expect somthing different from that.
You cannot take a aggregated column value and a column which is not aggregate together.
If i understand you requirement properly, you need

A B Group
3 x I
3 y I
3 z I
2 w II
2 x II

Column A is the total number of rows per group. Column B is the direct value.
To acheive this you may need to aggregate seperatly to find the number of row count. And need to do a join.
Or you can try the sort utility to sort based on the group and use the function of Create Key "Change Column to calculate number" of rows per group and can do further manipulation using a transformer.

- Kumar

dspxlearn · Post by **dspxlearn** » Mon Nov 14, 2005 5:30 am

Hi,

Kumar , what you have said is right...I have tried an other way around to accomplish my requirement.One more thing is , does the aggregator behaves the same way as an SQL query..??

kumar_s · Post by **kumar_s** » Wed Jun 14, 2006 11:21 pm

Just happend to see now...
SQL is a language to query database. Aggregator is a stage which help to acheive just the group by function available in SQL. Not sure what is your exact doubt?

ray.wurlod · Post by **ray.wurlod** » Wed Jun 14, 2006 11:57 pm

System variables and/or stage variables can be used to count things without the need to introduce an Aggregator stage.

Conceptually the Aggregator stage behaves like SQL, but physically it works differently. This is, and should be, transparent.