DSXchange

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

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

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

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.