topic Re: Selecting a variable length string from field. in QlikView

Selecting a variable length string from field.

Wed, 25 Nov 2020 16:16:04 GMT

G'day all,

Trying to come up with a way to extract a variable length string from a field. They are always defined within '<>'.

Example;

<VOICE> Issues w....

<MBK> can't...

<OTHER> unable to...

These are always located at the start of the string. Just need to extract content from between '<>'. Any ideas.

thanks

Jason

Re: Selecting a variable length string from field.

sunny_talwar — Fri, 13 May 2016 08:49:12 GMT

May be use Text between function here:

TextBetween(Example, '<', '>') to extract the stuff between <...>. Check out the sample below

Table:

LOAD *,

TextBetween(Example, '<', '>') as Requirement,

Len(TextBetween(Example, '<', '>')) as Len,

Len(TextBetween(Example, '<', '>')) + 2 as [Len_Inc_<>];

LOAD * Inline [

Example

<VOICE> Issues w....

<MBK> can't...

<OTHER> unable to...

];

Re: Selecting a variable length string from field.

Kushal_Chawda — Fri, 13 May 2016 08:57:05 GMT

If accidentally you get the Data like <VOICE> Issues.. <something> then TextBetween(YourField, '<', '>') will give you the wrong result, so use the below logic which will always pick the values from first string

TextBetween(YourField, '<', '>',1) as TextBetween,

len(TextBetween(YourField, '<', '>',1)) as Len_TextBetween

Re: Selecting a variable length string from field.

sunny_talwar — Fri, 13 May 2016 09:02:04 GMT

Kush I think by default it is 1. Check the attached image

Re: Selecting a variable length string from field.

sunny_talwar — Fri, 13 May 2016 09:06:08 GMT

Also check out the help section:

TextBetween ‒ QlikView‌

Re: Selecting a variable length string from field.

Sun, 15 May 2016 23:37:33 GMT

Thanks to both Sunny and Kushal. This worked a treat.

You learn something every day. Wasn't even aware this command existed!!

thanks again.

Jason.

Re: Selecting a variable length string from field.

Mon, 16 May 2016 10:32:21 GMT

first you have to assign TextBetween(Example, '<', '>') to extract the stuff between <...>.

<HAMMER> Issues.. <ANYTHING> then TextBetween(YourField, '<', '>') and https://goo.gl/fJU04B <HAMMER> Issues w....
<MBK>
<OTHER>