Splitting values within a field into individual rows on the table

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

];

[marcus_sommer]

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

[jaumecf23]

Attached you will find a solution that is working.

[sunny_talwar]

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/