extracting data using subfield?

[Anonymous]

Hi Guys,

I have an order number that has multiple (up to 20) reference numbers.

I'm trying to extract a field called reference numbers. These numbers are now horizontal displayed. See screenshot.

The problem is that I can't select 1 reference number. Instead I have to select 2/3 or more reference number in the reference field.

If I use the subfield statement I can only select one reference number e.g. Subfield(refnumber, ',', 1) would display the first reference number that's behind a comma that goed the same for subfield(refnumber, ',', 2), I would only get the second ref number .

How can I get all the ref. number vertically so I can select 1 reference number rather than 3 or more?

The other problem is that some ref. numbers are on the left side while others are on the right. Does that mean that qlikview does not recognize them as numbers?

Hope someone can help!

Thanks!

isam

[vijay_iitkgp · Accepted solution]

Hi,

Use Subfield(ReferenceNumber,',') AS RefNumber

It will give you all the reference number in row.

[Anonymous]

Thanks Vijay, I expect that the solution would be so easy! I was thing of al sorts of if senario's.

Thanks man!

[SunilChauhan]

in script use below

if(istext(refnumber)=0,text(refnumber),refnumber) as referenceno

then apply subfield

hope this helps

[Anonymous]

Thanks Sunil for your help!!