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
To Implement aagregate functionality without using aggregate
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 41
- Joined: Sat Jun 06, 2009 11:13 pm
- Location: Kolkata
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.
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.
Nayan
... 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.
All of this assumes you want the same number of records out of your "aggregation" activity rather than just the aggregated subset.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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>
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>
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.