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

[#1]

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

Customer	Items	Prices
1	A|B|C	10.00|20.00|30.00
2	B|D|G|Q	20.00|50.00|15.00|25.00

My desired result is:

Customer	Items	Prices
1	A	10.00
1	B	20.00
1	C	30.00
2	B	20.00
2	D	50.00
2	G	15.00
2	Q	25.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

[rwunderlich · Accepted solution]

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

[#3]

Thanks Rob. This is a wonderful approach.