Load Script - Break a string field into substrings and load in different rows

[ashish_2511]

Hi There

Below are snaps of input and output tables, Please suggest a way to achieve this in load script.

Input
ID	Data
1	Row1|Row2|Row3|Row4
Output
ID	Data
1	Row1
2	Row2
3	Row3
4	Row4

Thanks!

-Sneh

[Anil_Babu_Samineni]

Script:

LOAD ID, SubField(Data,'|') as Data Inline [

ID, Data

1, Row1 | Row2 | Row3 | Row4

];

[vinieme12]

Load

I'D,

Subfield (Data,'|') as Data

From xxxx;

[ashish_2511]

Anil - Thanks a lot for the quick turn around.

This didn't solve my problem completely as it gives me an output like this -

Whereas in expected output table - ID should be - 1,2,3,4

[ashish_2511]

Vineeth - Thanks a lot for the quick turn around.

This didn't solve my problem completely as it gives me an output like this -

Whereas in expected output table - ID should be - 1,2,3,4

[Anil_Babu_Samineni · Accepted solution]

May be thihs?

LOAD RowNo() as ID, SubField(Data,'|') as Data Inline [

ID, Data

1, Row1 | Row2 | Row3 | Row4

];

[ashish_2511]

Cool . This solves my problem completely.

Thanks!

Sneh

[vinieme12]

Try below, assuming an I'd relates to a group of data and is unique to each group , you need to use autonumber ()

Load

I'D,

AUTONUMBER (I'D, DATA) AS INSTANCE I'D,

Data;

LOAD

ID, SubField(Data,'|') as Data

From xxxx;