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

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

ashish_2511 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Anil_Babu_Samineni — Mon, 06 Feb 2017 05:13:54 GMT

Script:

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

ID, Data

1, Row1 | Row2 | Row3 | Row4

];

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

vinieme12 — Mon, 06 Feb 2017 05:14:34 GMT

Load

I'D,

Subfield (Data,'|') as Data

From xxxx;

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

ashish_2511 — Mon, 06 Feb 2017 05:29:27 GMT

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

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

ashish_2511 — Mon, 06 Feb 2017 05:29:50 GMT

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

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

Anil_Babu_Samineni — Mon, 06 Feb 2017 05:31:55 GMT

May be thihs?

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

ID, Data

1, Row1 | Row2 | Row3 | Row4

];

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

ashish_2511 — Mon, 06 Feb 2017 05:35:55 GMT

Cool . This solves my problem completely.

Thanks!

Sneh

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

vinieme12 — Mon, 06 Feb 2017 05:36:50 GMT

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;