topic extracting data using subfield? in QlikView

extracting data using subfield?

Anonymous — Wed, 11 Jan 2012 12:33:43 GMT

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

extracting data using subfield?

vijay_iitkgp — Wed, 11 Jan 2012 12:43:20 GMT

Hi,

Use Subfield(ReferenceNumber,',') AS RefNumber

It will give you all the reference number in row.

extracting data using subfield?

Anonymous — Wed, 11 Jan 2012 12:54:35 GMT

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

Thanks man!

extracting data using subfield?

SunilChauhan — Wed, 11 Jan 2012 12:59:42 GMT

in script use below

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

then apply subfield

hope this helps

Re: extracting data using subfield?

Anonymous — Wed, 11 Jan 2012 13:10:19 GMT

Thanks Sunil for your help!!