Duplicate rows based on value passed

boris_karloff · Post by **boris_karloff** » Fri Mar 14, 2008 8:26 am

I have a requirement to be able to duplcate rows in a DSet based on the number of months between two dates.

I am running out of options.

Can DataStage do this? is it possibe (if some one could point me in the right direction)?

If not I guess it would be a case calculating the months difference in DataStage and then using some external script to duplicate the records.

I would prefer to keep it all in DataStage, however if this is not the case then I can start persuing the other avenues,

Thanks,
B

mcs@rajesh · Post by **mcs@rajesh** » Fri Mar 14, 2008 9:14 am

Hi
Am sorry that i can't understand your requirement can you be more specific so that i may try to help you.

ArndW · Post by **ArndW** » Fri Mar 14, 2008 9:22 am

Yes, DataStage can do this, but not with a simple click. First, you need to create a list of elements, in your case the months, i.e. 2007/6 and 2008/3 would result in a string "2007/06,2007/07,2007/08,2007/09,2007/10,2007/11,2007/12,2008/01,2008/02,2008/03". Once you have this string you can use a pivot stage to create one row per value.

boris_karloff · Post by **boris_karloff** » Fri Mar 14, 2008 9:23 am

mcs@rajesh,

No problem,

We have a rows of data i.e.

Start Date , End Date, value 1, value 2.

I need to calculate the months between start and end date (which I think I have got through a transformer).

I then need to duplicate this row based the number of months between the two dates.

So if the differen was 3 I would like to output 3 new rows.

Start Date , End Date, value 1, value 2, Month 1(JAN 1)
Start Date , End Date, value 1, value 2, Month 2(FEB 2)
Start Date , End Date, value 1, value 2, Month 3(MAR 3)

Any more info please ask

boris_karloff · Post by **boris_karloff** » Mon Mar 17, 2008 6:09 am

Thanks ArndW,

I am struggling a bit to get this string built in a transformer, am I corect in thinking that I can't do this in a loop in a transformer.

Can any one offer any help to get me started.

Thanks,
B

ArndW · Post by **ArndW** » Mon Mar 17, 2008 7:37 am

You will have to write a loop in this case since you have 2 values, the start and end. This would best be done with a wrapper stage.

boris_karloff · Post by **boris_karloff** » Mon Mar 17, 2008 9:03 am

Thanks,

Please excuse my ignorance but I don't seem to find a wrapper stage in my pallette, even when I look to customize my pallette.

Is this and external function ?

B

ArndW · Post by **ArndW** » Mon Mar 17, 2008 10:32 am

Bad wording on my part, it isn't a wrapper stage, but putting together a wrapper. You can add a "parallel routine" in the routines section. At v 7 you would do that in the manager, at v8 you can do it right in the designer.