Can aggregator stage be of some help
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Participant
- Posts: 40
- Joined: Mon May 11, 2009 12:19 am
- Location: Madurai
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Participant
- Posts: 40
- Joined: Mon May 11, 2009 12:19 am
- Location: Madurai
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Participant
- Posts: 40
- Joined: Mon May 11, 2009 12:19 am
- Location: Madurai
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
thanks for your reply I will try that ....another thing going back to my original message
I want to load table B having summarised data of Table A based on th ekey fields and before loading this summarised data the profit percentage on both the key field seperately should be calculated...
This is what I am going to do
1)Extract the summarised data by grouping on keycolumns from the table and load it in a Data set .. (because it is standard practise to divide a load in three parts Extract,Transform,Load)
2)then do all the transformations in a seperate job to drive the final result
3)lastly load this data to the target summary table..
what r your thoughts on this..
I want to load table B having summarised data of Table A based on th ekey fields and before loading this summarised data the profit percentage on both the key field seperately should be calculated...
This is what I am going to do
1)Extract the summarised data by grouping on keycolumns from the table and load it in a Data set .. (because it is standard practise to divide a load in three parts Extract,Transform,Load)
2)then do all the transformations in a seperate job to drive the final result
3)lastly load this data to the target summary table..
what r your thoughts on this..
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Example of Fork Join Design
Code: Select all
----> Copy ----------------> Join ---->
| ^
| |
+----> Aggregator -----+
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.
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
Re: Example of Fork Join Design
Hi
As I want total profit of all countries
I am using aggregrator stage twice ..first time I group on CountryID then I get sum of profit of particular country like if there are two countries Eng and Ind I will get two results sum of profit of Ind and sum of profit of Eng but I want total profit of all countries so I am again aggregrating on a dummy column where its value is 1..then I get the desired result..
Now the problem is that when I do that I dont get the key in the result because I dont use t as grouping condition 2nd time ..if I use it I will get sum of profit seperate for seperate countries ..which I dont want ..so when I dont have the key feild ..I am not able to join ..
Is it possible to take the sum of all countries in a variable and use it as a parameter or variable when the calculation for percentage will be done...???
As I want total profit of all countries
I am using aggregrator stage twice ..first time I group on CountryID then I get sum of profit of particular country like if there are two countries Eng and Ind I will get two results sum of profit of Ind and sum of profit of Eng but I want total profit of all countries so I am again aggregrating on a dummy column where its value is 1..then I get the desired result..
Now the problem is that when I do that I dont get the key in the result because I dont use t as grouping condition 2nd time ..if I use it I will get sum of profit seperate for seperate countries ..which I dont want ..so when I dont have the key feild ..I am not able to join ..
Is it possible to take the sum of all countries in a variable and use it as a parameter or variable when the calculation for percentage will be done...???
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you've got version 8.5 you can do that in the Transformer stage using the new internal loop.
Otherwise use the fork join design, having created a join key (perhaps @INROWNUM in a Transformer stage would do it). If you need a nested fork join design then so be it - it works, indeed there's an exercise in my Advanced DataStage class that does precisely that.
Otherwise use the fork join design, having created a join key (perhaps @INROWNUM in a Transformer stage would do it). If you need a nested fork join design then so be it - it works, indeed there's an exercise in my Advanced DataStage class that does precisely that.
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.
-
- Participant
- Posts: 94
- Joined: Sun Jul 18, 2010 11:35 pm
-
- Premium Member
- Posts: 258
- Joined: Tue Jul 04, 2006 10:35 pm
- Location: Toronto