Can aggregator stage be of some help

[tanaya.deshpande@tieto.co]

I mean how can I calculate Profit of all product, Profit of all country

[priyadharsini]

Do you want to calculate the profit percentage based on Product id and country id or using Product id adn country id seperately?

[tanaya.deshpande@tieto.co]

I want to calculate it seperately ..

[priyadharsini]

use an aggregator to get the sum of all profits based on Product and then use this value in the next transformer to get the profit%

[tanaya.deshpande@tieto.co]

that is ok ...but how can i get sum of profit grouping on country iD and productID seperately on same aggregator stage ..is that passible

because only one summery column gets mapped to output

[tanaya.deshpande@tieto.co]

if not aggregator how ccan this be acheived...

[priyadharsini]

use 2 aggregator stage and then join it back based on the common key.

[tanaya.deshpande@tieto.co]

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..

[ray.wurlod]

Fork-join (twice) with two Aggregator stages.

[tanaya.deshpande@tieto.co]

what is fork join?

[ray.wurlod]

Code: Select all

----> Copy  ----------------> Join ---->
        |                       ^
        |                       |
        +----> Aggregator  -----+

[tanaya.deshpande@tieto.co]

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...???

[ray.wurlod]

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.

[tanaya.deshpande@tieto.co]

lets forget all the discussion uptill now ........can any one tell how to impliment my original requirement..........Ineed to calculate Profit of ABC on each level ProductID level ..Countrylevel,monthly level...

[samyamkrishna]

This can be achived by what Ray suggested.
Fork Join.