DSXchange

Hi All

I need to sum the amounts(100,200,-300 etc) based on code(abc,def,gkt etc) key column,but i want this implementation to be done without using aggration stage

Since using aggration stage,we cannot carry fwd remaining columns for further transformation.Though we can do self join after aggregation with other columns ,key columns are differennt here

Please suggest a solution to this

Thanks
Vidya

You can use transformer stage and implement the below logic in the stage variables to produce the funcionality similar to an aggregator stage. The pre-requisite to use this stage is that the data should be sorted on the keys columns that will be used for group by clause, here it is abc, def, gkt, etc.

CURR_KEY (SV1) -> abc:def:gkt:...
AMT(SV2) -> If CURR_KEY = PREV_KEY Then AMT + SV2 Else AMT
PREV_KEY(SV3 :initialise it to '') -> CURR_KEY

Now remove duplicates from the output of transformer stage and retain the last row. The amount value present in that row will give you the aggregated value.

... however, you'll still need the "do self join after aggregation" part to put this back together with the remaining columns. Might as well stick with the Aggregator and figure out how to get the fork join working properly. Sure seems like you would be able to use this "code" column you are grouping on.

All of this assumes you want the same number of records out of your "aggregation" activity rather than just the aggregated subset.

Why "without using Aggregator stage"?

The correct philosophy is "the right tool for the right job" and, to aggregate data, the right tool is the Aggregator stage.

<rant>
Resist stupid requirements!
</rant>