topic Re: Splitting values within a field into individual rows on the table in QlikView

Splitting values within a field into individual rows on the table

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

Hi,

I have this data:

Key	Type	Assoc Ids
1	AAA	123
2	BBB	555-234-123
3	CCC	-123-999

Notice that [Assoc Ids] has a delimiter. Based on this delimiter, I need to split the values into individual rows and copy the data for the other fields onto each of those rows.

Resulting table would be like this:

Key	Type	Assoc Ids
1	AAA	123
2	BBB	555
2	BBB	234
2	BBB	123
3	CCC	123
3	CCC	999

Re: Splitting values within a field into individual rows on the table

sunny_talwar — Tue, 17 Apr 2018 12:25:53 GMT

Try this

LOAD *

Where Len(Trim([Assoc Ids])) > 0;

LOAD Key,

Type,

SubField([Assoc Ids], '-') as [Assoc Ids];

LOAD * INLINE [

Key, Type, Assoc Ids

1, AAA, 123

2, BBB, 555-234-123

3, CCC, -123-999

];

Re: Splitting values within a field into individual rows on the table

marcus_sommer — Tue, 17 Apr 2018 12:26:55 GMT

You could just use: subfield([Assoc Ids], '-') within the load of the table to create these records. Subfield without a third parameter worked like a loop.

- Marcus

Re: Splitting values within a field into individual rows on the table

jaumecf23 — Tue, 17 Apr 2018 12:29:31 GMT

Attached you will find a solution that is working.

Re: Splitting values within a field into individual rows on the table

sunny_talwar — Tue, 17 Apr 2018 12:34:07 GMT

I guess we have similar approaches, but I suggest using Preceding load based on some testing done by Rob

https://qlikviewcookbook.com/2018/03/preceding-load-performance-update/