topic Extracting a value from a string in QlikView

Extracting a value from a string

richard_chilvers — Tue, 01 Apr 2014 08:14:05 GMT

My table has a field which contains notes with a value in. For example:

"The cost of the purchase is £234.56 payable by cheque"

I wish to extract the numeric part as a value.

I have used TextBetween(NOTE_TEXT,'£',' ') AS Value

which works well, except sometimes there is no space after the value, eg.

"The cost of the purchase is £234.56per person payable by cheque".

Is there anyway I can identify the TextBetween '£' and a non-numeric character?

Thanks

Re: Extracting a value from a string

kangaroomac — Tue, 01 Apr 2014 08:19:30 GMT

Hiya,

Have you tried the keepchar function?

KeepChar(NOTE_TEXT)

Re: Extracting a value from a string

its_anandrjs — Tue, 01 Apr 2014 08:19:48 GMT

Use Keepchar fuction like

=KeepChar(Text,'0123456789')

=Keepchar('The cost of the purchase is £234.56 payable by cheque','0123456789')

Hope this helps

Re: Extracting a value from a string

its_anandrjs — Tue, 01 Apr 2014 08:23:06 GMT

For your field

Keepchar(NOTE_TEXT,'0123456789') AS Value

Hope this helps

Re: Extracting a value from a string

kangaroomac — Tue, 01 Apr 2014 08:28:02 GMT

And if you want to knit pick, add the full stop decimal indicator (.) as well - else the cost of the purchase is going to be a bit more than expected.

i.e.

Keepchar(NOTE_TEXT,'0123456789.') AS Value

Re: Extracting a value from a string

richard_chilvers — Tue, 01 Apr 2014 08:28:42 GMT

Thanks for a good suggestion.

Keepchar sounds good, but it won't work in my case; for example, the note might say:

"The cost of the purchase is £234.56 payable by cheque which covers 15 people".

So I need to only capture the value after the £ sign.

The challenge we all face with unstructured data

Re: Extracting a value from a string

kangaroomac — Tue, 01 Apr 2014 08:34:32 GMT

Would this help?

Subfield(Keepchar(NOTE_TEXT,'0123456789.') , '$') AS Value

or the three parameter version:

Subfield(Keepchar(NOTE_TEXT,'0123456789.') , '$', 1) AS Value

(Substitute the dollar for a pound sign)

Re: Extracting a value from a string

its_anandrjs — Tue, 01 Apr 2014 08:37:50 GMT

If you want to keep decimal value also then write

Keepchar(NOTE_TEXT,'0123456789.') AS Value

Re: Extracting a value from a string

tresB — Tue, 01 Apr 2014 09:02:59 GMT

Try like this, using FindOneOf() and Index()

=Mid('The cost of the purchase is £234.56per person payable by cheque',Index('The cost of the purchase is £234.56per person payable by cheque', '£')+1,FindOneOf(Mid('The cost of the purchase is £234.56per person payable by cheque',Index('The cost of the purchase is £234.56per person payable by cheque', '£')),'abcdefghijklmnopqrstuvwxyz')-2)

Generic:

=Mid(YourTextField,Index(YourTextField, '£')+1,FindOneOf(Mid(YourTextField,Index(YourTextField, '£')),'abcdefghijklmnopqrstuvwxyz')-2)

Re: Extracting a value from a string

ashwanin — Tue, 01 Apr 2014 09:05:46 GMT

Use Keepchar(NOTE_TEXT)

Re: Extracting a value from a string

Tue, 01 Apr 2014 09:17:08 GMT

Here you go..

SubField(SubField(NOTE_TEXT,'£',2),' ')

Thanks,

Prabhu