Can aggregator stage be of some help

[tanaya.deshpande@tieto.co]

this is my input data

Product Month Country Profit
ABC 201001 IND 30
ABC 201001 CHI 40
ABC 201003 JPM 50
ABC 201004 USA 23
ABC 201005 UK 45
ABC 201005 IND 60
XYZ 201001 IND 34
XYZ 201001 CHI 56
XYZ 201003 MAL 74
XYZ 201004 USA 23
XYZ 201005 UK 34
XYZ 201005 UK 45


this is my output data

Product Month Country Profit Profit Share in %
ABC 201001 IND 30 100
ABC 201001 CHI 40 100
ABC 201003 JPM 50 100
ABC 201004 USA 23 100
ABC 201005 UK 45 100
ABC 201005 IND 60 100
XYZ 201001 IND 34 100
XYZ 201001 CHI 56 100
XYZ 201003 MAL 74 100
XYZ 201004 USA 23 100
XYZ 201005 UK 79 100
ABC 201005 N/A 105 42.33
N/A 201001 N/A 160 31.00
N/A 201001 IND 64 0.40

here profit share is calculated as (Profit at product level at month level at country level)

How can I acheive this in DS

[ray.wurlod]

Fork join.

Probably two nested fork joins.

[tanaya.deshpande@tieto.co]

well that is fine too...but how will I take unique key values..

[ray.wurlod]

Add them to your design, using Transformer or Column Generator stage, as already mentioned.

[blazDelParis]

if you are in the hurry, not in easy feeling with datastage, and if you are using Oracle database (at least 9i version) , you can use the analytic functions of the database in the oracle stage :

Code: Select all

with TEMP as ( 
  select country, region, myProduct, 
  sum(price*quantity) over (partition by country, region, myProduct) as profit_per_product,
  sum(price*quantity) over (partition by country, region) as profit_per_region,
  sum(price*quantity) over (partition by country) as profit_per_country,
  sum(price*quantity) over() as profit_total,
  from 
    myTable
)
select 
  profit_per_product, round( profit_per_product/profit_total*100,2) as pct_product,
  profit_per_region, round( profit_per_region/profit_total*100,2) as pct_region,
  profit_per_country, round( profit_per_country/profit_total*100,2) as pct_country,
  profit_total
from
  TEMP

[tanaya.deshpande@tieto.co]

Thanks a lot guys my problem is resolved..

[ray.wurlod]

Care to share how you resolved it?