Duplicate records based on col's data

[tbtcust]

Hello All,

I have a task to duplicate/repeat records in a file based on a particular column. All columns are separated by "|". This particular column has codes that are separated by commas.

As an example, if there are three codes on one record, that record should be outputted three times, with the code in the first position. See below. Thanks in advance for any help.

Example
Input record
Field1 | Field2 | Field3 | 9, 8, 7

Output Records
9 | Field1 | Field2 | Field3
8 | Field1 | Field2 | Field3
7 | Field1 | Field2 | Field3

[asorrell]

What specific release of DataStage 8 are you on? If you are on 8.7 or later I believe this can be done with a transformer loop. On any release of 8 you can use the Pivot stage to do a horizontal pivot. Look it up in the Parallel Job Developer's Guide, the example there is almost exactly what you are doing.

[tbtcust]

Thank you for the reply and suggestion asorrell.

I looked at the Transform Loop and Pivot Stage. They both seem to rely on the codes being in separate columns. This is not the case for me. I need to duplicate a record based on the content of one column where the content of that one column is separated by commas. So

the input record looks like this
Field1 | Field2 | Field3 | 9, 8, 7


Output Records will be:
9 | Field1 | Field2 | Field3
8 | Field1 | Field2 | Field3
7 | Field1 | Field2 | Field3

[chulett]

The Pivot stage does require separate columns but Transformer Looping has no such requirement.

[tbtcust]

Thank you chulett. I was relying on the documentation example way too much.

I have coded the transformation loop and it works fine.

Thanks again.

[chulett]

Excellent. :D