Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Splitting values within a field into individual rows on the table

Hi,

I have this data:

KeyTypeAssoc Ids
1AAA123
2BBB555-234-123
3CCC-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:

KeyTypeAssoc Ids
1AAA123
2BBB555
2BBB234
2BBB123
3CCC123
3CCC999
1 Solution

Accepted Solutions
sunny_talwar

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

];

View solution in original post

4 Replies
sunny_talwar

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
Creator III
Creator III

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/