Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
markramiro
Contributor 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

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

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

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

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

MVP & Luminary
MVP & Luminary

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

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

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

Attached you will find a solution that is working.

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

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/