Need logic for the following data
ST CD AMT
100 X 10
100 Y 20
100 Z 30
101 X 10
101 Y 20
101 Z 30
102 X 10
102 Y 20
102 Z 30
Output will be
ST X Y Z
100 30 60 90
101 30 60 90
102 30 60 90
Currently I am using 3 Aggregate states but is there any other way to implement this ?
ISSUE: I have 10 codes to Aggregate I am using 10 Aggregate stages to get the out put
Columns to rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
If you are using DS 8.5 or above, consider using Transformer Loop function. For reference check http://newtonapples.com/datastage-trans ... looping-1/
Or if you are using DS 8.1 try using key break logic viewtopic.php?p=423754
Or if you are using DS 8.1 try using key break logic viewtopic.php?p=423754
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
Take two output link from source. In one output link do aggregation on amt based on CD. Your output will be below:-
X,30
Y,60
Z,90
In another output,you need to pivot CD based on ST,output should be:-
100 X Y Z
101 X Y Z
102 X Y z
Now do left outer join,making link2 on left,and in output of join stage pass sum amount,it is your expexted output.
Take two output link from source. In one output link do aggregation on amt based on CD. Your output will be below:-
X,30
Y,60
Z,90
In another output,you need to pivot CD based on ST,output should be:-
100 X Y Z
101 X Y Z
102 X Y z
Now do left outer join,making link2 on left,and in output of join stage pass sum amount,it is your expexted output.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/