Hi All,
i have requirement were i have last rolling 13 months of data comming from source for ex
source data
year month agntid group subgrp rev vol
2013 6 abc123 first abc 213 12
2012 12 abc123 first abc 17 14
Expected Target data
agntid group subgrp julyrev julvol junerev junevol mayrev mayvol aprilrev aprilvol marchrev marchvol febrev febvol janrev janvol decrev decvol novrev novvol octrev octvol seprev sepvol augrev augvol julyrev julyvol
abc123 first abc 0 0 213 12 0 0 0 0 0 0 0 0 0 0 17 14 0 0 0 0 0 0 0 0 0 0
But iam getting o/p as
agntid group subgrp julyrev julvol junerev junevol mayrev mayvol aprilrev aprilvol marchrev marchvol febrev febvol janrev janvol decrev decvol novrev novvol octrev octvol seprev sepvol augrev augvol julyrev julyvol
abc123 first abc 213 12 17 14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
I am using pivot stage to parse . below are the properties
Array size is 13, group by agntid, pivot on rev and vol
Any suggestions?
Thanks
vertical pivot parsing issue
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Pivot stage is working perfectly, but it does not know that your output should be based on month value. You can use transformer and create 26 stage variables and initialize 0. For ex:
Julyrev=if month=7 then rev else julyrev
Julyvol=if month=7 then vol else julyvol
.................
Pass these variables to output link.
Then use remove duplicate stage and keep last row in a group
Column=group
Julyrev=if month=7 then rev else julyrev
Julyvol=if month=7 then vol else julyvol
.................
Pass these variables to output link.
Then use remove duplicate stage and keep last row in a group
Column=group
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/
Hi Prasoon,
Thanks for immediate response, I agree with you if it is not rolling month.
How can we achieve the results if it is rolling months? Let me eloborate a little
source data
year month agntid group subgrp rev vol
2013 6 abc123 first abc 213 12
2012 12 abc123 first abc 17 14
If the job is run on currentdate which sept 2 then target data should be
Target data
agntid group subgrp augrev augvol julyrev julvol junerev junevol mayrev mayvol aprilrev aprilvol marchrev marchvol febrev febvol janrev janvol decrev decvol novrev novvol octrev octvol seprev sepvol augrev augvol
abc123 first abc 0 0 0 0 213 7 0 0 0 0 0 0 0 0 0 0 17 14 0 0 0 0 0 0 0 0
simmilarly if its run for next month(oct) for same source data then it will move a step right.
Thanks
Thanks for immediate response, I agree with you if it is not rolling month.
How can we achieve the results if it is rolling months? Let me eloborate a little
source data
year month agntid group subgrp rev vol
2013 6 abc123 first abc 213 12
2012 12 abc123 first abc 17 14
If the job is run on currentdate which sept 2 then target data should be
Target data
agntid group subgrp augrev augvol julyrev julvol junerev junevol mayrev mayvol aprilrev aprilvol marchrev marchvol febrev febvol janrev janvol decrev decvol novrev novvol octrev octvol seprev sepvol augrev augvol
abc123 first abc 0 0 0 0 213 7 0 0 0 0 0 0 0 0 0 0 17 14 0 0 0 0 0 0 0 0
simmilarly if its run for next month(oct) for same source data then it will move a step right.
Thanks
Same basic answer applies. It is your job to put the data in the proper column based on your month rules and the current run date. I don't really see that the fact that it is a "rolling month" is significantly changing what you need to do.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers