Generate Multiple Rows from a single row

[bjcripe]

Hi,

I am using Server and trying to generate a job to read in a record, divide one field into multiple rows. Example --
Record in informix source
Record 1
Field 1: A
Field 2: XYZ
Records needed in informix target
Record 1
Field 1:A
Field 2:X
Record 2
Field 1:A
Field 2:Y
Record 3
Field 1:A
Field 2:Z

both fields are dynamic so I will have to loop through until there is no more data in field Field 2 before moving on to the next record.

Thanks in advance --- Brian

[rasi]

hi

if you can bring your input into fixed fields then you can use Pivot stage. Otherwise you need to code this in basic.

Thanks
Rasi

[ray.wurlod]

Are there always exactly three characters in Field002? If so, you can use something like this.

Code: Select all

source ---->  Transformer  ----->  SeqFile  ----->  Target

The Transformer stage writes a single column to the SeqFile stage, of the form

Code: Select all

Key:",":Field001:",":Field002[1,1]:NL:Key:",":Field001:",":Field002[2,1]:NL:Key:",":Field001:",":Field002[3,1]

NL is the appropriate new-line character(s) for your operating system.
Output from the SeqFile stage treats it as a regular comma-delimited file.

[ds_developer]

Is Field 2 a multi-valued field, like from a Universe database? The Universe stage allows you to "Normalize" on a multi-valued field. This method allows you to handle a varying number of values in Field 2 (which is what I think you mean when you say 'dynamic').

Hope this helps,
John