splitting of a row into multiple rows

[shaonli]

Hi,

My i/p file has 3 columns.Say Order no,Order Name.Order Location.
The O/p File has two fields Order No,Order Location code
I/P-
100,A,ABC
200,B,XYZ
300,C,MNO

O/P-
100,10
200,20
200,20
200,20
300,30
300,30

That means depending upon Location name the I/p gets split into multiple records.
When Location name=XYZ the o/p becomes 3
When Location Name=MNO the o/p is 2

Please suggest which stage to use to split the records depending on the condition

Thanks in advance
Shaonli

[chulett]

First thought would be to join the input with a small set with static location values - 3 XYZ records, 2 MNO records, etc. Then just let the join generate the correct number of records. Don't know if that means a stage or a database solution, but that's the first thing that popped into my head.

[vmcburney]

A lookup stage that supports multiple rows from the reference link should let you expand your row count. In the example if you join on the first field (100,200,300) 3 rows will go in and 6 will come out made up of fields from both records.

[Teej]

Definitely the Lookup stage or join stage allowing for multiple matches.

[shaonli]

there will be slight change in the o/p
100,10
200,20
200,21
200,22
300,30
300,31


For location=xyz it will get splitted into 3 rows but location code will be different.for 3rd condition also the row will get splitted into two columns and location code will be different in two rows.

Please suggest.

Thanks in advance
Shaonli