Aggregator (Counting grouped Data)

dstager · Post by **dstager** » Mon May 25, 2009 2:21 pm

Hi DS Experts,

I am trying to 'count' records grouped by a certain ID and sorted by Date.

Example:

Input Data:

RowID Sale_Type Date
ABC A1 Jan 1
ABC C2 May 21
BCE A1 Aug 3
DFE K1 Feb 3
ABC Z4 Feb 5
BCE A1 Mar 1
DFE G4 Jan 2

Desired Output:

RowID Sale_Type Date Counter
ABC A1 Jan 1 1
ABC Z4 Feb 5 2
ABC C2 May 21 3
BCE A1 Mar 1 1
BCE A1 Aug 3 2
DFE G4 Jan 2 1
DFE K1 Feb 3 2

In Summary, for every unique RowID (grouped), I would like initiate a counter (incremented by 1) and sorted by date.

Please help.

Thanks.

chulett · Post by **chulett** » Mon May 25, 2009 2:41 pm

Since there's no real 'aggregation' - a couple of thoughts are here, more can be found by searching for 'key change column'.

dstager · Post by **dstager** » Mon May 25, 2009 3:32 pm

I tried the solution below, but the sVValue keeps incrementing (it does not revert back to 1 when a new key grouping is identified).

How do I initialize a variable? Do I just set the 'initial value' to 1?

Thanks.

Simple solution will be to use stage variables. Try something like this....
Sort the data on your three columns.
Initialize sVValue as 1
Code:

sVCurrent = COLA:COLB:COLC
sVValue = If sVCurrent = sVPrevious then sVValue+1 else sVValue
sVPrevious = sVCurrent

And use sVValue in your column derivation of INT column.

chulett · Post by **chulett** » Mon May 25, 2009 3:41 pm

You need to set it back to 1 rather than increment it when the group changes. In other words, change that "else SVValue" to "else 1". That Initial Value is an 'at the moment the job starts' value.

dstager · Post by **dstager** » Tue May 26, 2009 8:27 am

Thank you for your help!

vinothkumar · Post by **vinothkumar** » Tue May 26, 2009 1:10 pm

How can I achieve the same in Server Edition ?

chulett · Post by **chulett** » Tue May 26, 2009 1:21 pm

Same way.

Perwezakh · Post by **Perwezakh** » Tue Nov 10, 2009 5:10 pm

chulett wrote:You need to set it back to 1 rather than increment it when the group changes. In other words, change that "else SVValue" to "else 1". That Initial Value is an 'at the moment the job starts' value.

Hi Criag,
Thanks for your reply here. One question.. I am using the same logic and in Parallel env, its not working.. Its creating Two Sets of identical rows (Key Column and Counter) because my process is running on two Nodes. Please advice how to fix this issue.
Thanks in advance..

Kryt0n · Post by **Kryt0n** » Tue Nov 10, 2009 5:33 pm

Make sure you partition correctly

chulett · Post by **chulett** » Tue Nov 10, 2009 6:51 pm

Or run on one node.