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: 
Not applicable

Further variations for DOC-4657: The Secret Life of SubField

I have recently working on a data model contains the following data. I found the following document helpful for explaining SubField() function.

The Secret Life of SubField

However the approach is not quite right when you have more than 1 column contains the same number of substrings and need to split

I have the following data:

FactA

CustomerItemsPrices
1A|B|C10.00|20.00|30.00
2B|D|G|Q20.00|50.00|15.00|25.00



My desired result is:

CustomerItemsPrices
1A10.00
1B20.00
1C30.00
2B20.00
2D50.00
2G15.00
2Q25.00



if i'm using this:

load

  customer,

  subfield(item,'|') as item,

  subfield(price,'|') as price

resident

   FactA;

it will give me 3 ^ 3 + 4 ^ 4 = 27 + 256 = 283 rows. This is definitely not what I want.

In order to get the desired result, here is my approach:

Load

  customer,

  SubField(item,'|') as item,

  SubField(price,'|') as price

Resident

   FactA;

join

load

  RowNo() as rownum,

  customer,

  subfield(price,'|') as price

resident

  FactA;

drop field rownum;

And it gives me the desired result.

This is my first post in this forum. I'm sure there are smarter approaches and please kindly share if you have one.

Cheers

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Your solution doesn't give me the desired result when I try it. But here is how I would approach the problem.

LOAD

  Customer,

  subfield(Items,'|', IterNo()) as item

  ,subfield(Prices,'|', IterNo()) as price

FROM

[https://community.qlik.com/thread/179099]

(html, codepage is 1252, embedded labels, table is @1)

WHILE iterno() <= SubStringCount(Items,'|')+1;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Your solution doesn't give me the desired result when I try it. But here is how I would approach the problem.

LOAD

  Customer,

  subfield(Items,'|', IterNo()) as item

  ,subfield(Prices,'|', IterNo()) as price

FROM

[https://community.qlik.com/thread/179099]

(html, codepage is 1252, embedded labels, table is @1)

WHILE iterno() <= SubStringCount(Items,'|')+1;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable
Author

Thanks Rob. This is a wonderful approach.