Column with not fixed number of delimited values

[Sham9]

Hello,

I have a requirement where my source is oracle table and one of the column containes value separated by commas. I want to create individual record for these values. For example

Input
------

Colum1| Column 2
123456| 2,5,6,100,45,51

Output
--------

Colum1| Column 2

123456|2
123456|5
123456|6
123456|100
123456|45
123456|51

I know with transformer loop option, I can create multiple rows from single row, but no idea how I can get individual value in column2 for each record. I think it might have worked with a pivot stage if the maximum number of comma in second column is known. But I unfortunately the comma in Column2 is no upper cutoff, In other words I do not have a clue on the number of values in column2.

Really sorry if It is a repeat question, I cannot find answer when I searched. Please share the link if this is already discussed

Any help is greatly appreciated !!

[ray.wurlod]

No, do use a loop. There are examples documented. Create a stage variable containing the number of delimited fields (use DCount() function, for example). Loop exit condition is

Code: Select all

 @ITERATION <= svNumberOfFields

Within the loop, extract an individual field using

Code: Select all

Field(in.Column, delimiter, @ITERATION, 1)

For example, Field(in.Column2, ",", @ITERATION, 1)
Load this into a loop variable and use that to provide the value of the output field. The second example in this IBM Knowledge Center article will provide a similar explanation.

[Sham9]

Thank you Ray!

My bad, I never thought about @ITERATION option. Thank you very much.It is working for me.

[chulett]

Pardon a (perhaps) silly question but I'm curious about transformer looping and how one controls that. Is there some other way rather than using @ITERATION to control how many times to loop?

[ray.wurlod]

Any expression.

For example a constant 10 if you always have 10 delimited fields.

But @ITERATION is there, and singularly useful.

Other techniques would be appropriate in the "filling in the gaps in a sequence of dates/numbers" scenarios.