Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
markramiro
Creator II
Creator II

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
MVP & Luminary
MVP & Luminary

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/