Aggregator, Restricting count for individual group

Munish · Post by **Munish** » Thu Jan 18, 2007 9:56 pm

Hi There,
I am using aggregator to do the sum of values.
The data set looks like
Account_Key, Service_Key, Period_Key, Called_Num,Charge_Amt

I am aggregating by Charge_Amt

For Account_Key, Service_Key, Period_Key group there are more than 10000 Called_Num.
and there are several Service_Key for Account_Key and Period_Key combination.

I want to restrict to top 100 for Account_Key, Service_Key, Period_Key group.

What might be the best solution.

Thanks and regards,
Munish

us1aslam1us · Post by **us1aslam1us** » Thu Jan 18, 2007 11:10 pm

Your statements are not clear but to my understanding you can sort in the ascending order on those keys and restrict them to 100 by giving a constraint in the transformer. But if you can pass some more information about what you are trying to achieve someone can help it.

Munish · Post by **Munish** » Thu Jan 18, 2007 11:32 pm

Data before passing to AGGREGATOR
Acc_K Serv_K Period_K Called_N Amt
1 2 3 88888 10
1 2 3 88888 12
------so on-----------------------------
1 2 3 88889 2
1 2 3 88889 3
---------so on---------------------------
more than 10000 called_num for this group (1,2,3)

1 3 3 77777 10
1 3 3 77777 12
------so on-----------------------------
1 3 3 77779 2
1 3 3 77779 3
---------so on---------------------------
more than 10000 called_num for this group (1,3,3)

After Aggregation it looks like
Acc_K Serv_K Period_K Called_N Amt
1 2 3 88888 22
1 2 3 88889 5

---------so on---------------------------
more than 10000 called_num for this group (1,2,3)

1 3 3 77777 22
1 3 3 77779 5
---------so on---------------------------
more than 10000 called_num for this group (1,3,3)

WishList:

What I want to restrict Top 100 for each combination
(1,2,3), (1,3,3)

Should you need more information, please let me know.

Thanks,
Munish

us1aslam1us · Post by **us1aslam1us** » Fri Jan 19, 2007 12:05 am

You can approach in this way

1.Sort the data on Acc_K,Serv_K,Period_K and Amt field in the Ascending order.
2. In Transformer stage, group the data based on (Acc_K Serv_K Period_K Called_N) and select the first 100 rows for each set.

Munish · Post by **Munish** » Fri Jan 19, 2007 12:27 am

Good Thought.

Is there any property in Aggregator to take care of this.

Thanks
Munish

ray.wurlod · Post by **ray.wurlod** » Fri Jan 19, 2007 5:27 am

No.

Whatever you do, you are going to require two passes through the data to do anything with ranks.

Munish · Post by **Munish** » Sun Jan 21, 2007 5:55 pm

Hi,
Could you please elabortate a bit further

1.Sort the data on Acc_K,Serv_K,Period_K and Amt field in the Ascending order.
2. In Transformer stage, group the data based on (Acc_K Serv_K Period_K Called_N) and select the first 100 rows for each set.

How do you do this in transformer.

Thanks
Munish

DSguru2B · Post by **DSguru2B** » Sun Jan 21, 2007 6:30 pm

Sorting is easy. Either an OS level or sort stage would do.
Do a group comparison of present row with previous row. If same start a counter.
In the transformer, restrict counter <= 1000. Then pass it through the aggregator and do your rollup.

ray.wurlod · Post by **ray.wurlod** » Sun Jan 21, 2007 7:39 pm

I understood the requirement to be to sort the aggregated data. This would involve stages in a different order; a sort (or Sort Merge collector) following the Aggregator stage and something (Head, Tail or Transformer stage perhaps) to filter the first or last 100 rows.

DSguru2B · Post by **DSguru2B** » Sun Jan 21, 2007 8:48 pm

The OP wants only first 100 enteries per group. This would require a counter in a transformer to constraint the output to the first 100 per group. Then pass it through the aggregator to do the rollup.
That was my understanding.

kumar_s · Post by **kumar_s** » Sun Jan 21, 2007 9:16 pm

Sort the data, use KeyChange column for the counter. Use transformer (or Filter stage) contraint to avoid KeyChange < 100, and following by an aggregator stage.

DSguru2B · Post by **DSguru2B** » Sun Jan 21, 2007 9:20 pm

kumar_s wrote:contraint to avoid KeyChange < 100,

Someone forgot the '=' along with '<'

kumar_s · Post by **kumar_s** » Sun Jan 21, 2007 9:24 pm

Count starts with 0.

DSguru2B · Post by **DSguru2B** » Sun Jan 21, 2007 9:26 pm

Ok you got me. If the count starts from 0 then yes. Darn you Kumar

Munish · Post by **Munish** » Sun Jan 21, 2007 11:21 pm

Hi All,
I am very near to solve this mystery.
as per the suggestions, what I plan to do
Sort>> Aggregate >> Sort (Group and respective amt (descending)) >> Another Sort (For Keychange)>> Filter stage (restrict Keychage Counter)

However, I am held up at

use KeyChange column for the counter

when I use 'Create Keychange Column' in Sort stage, it does not give me right result.
Can you please put some light how to get it running.

Thanks,
Munish