topic Re: extract a text from a string in QlikView

extract a text from a string

Anonymous — Wed, 10 Feb 2016 14:29:19 GMT

Hi All!

I need to get the position inside a string from an specific text. For example

The field Tracking has the following content

0x|cam:0000|adg:0000|kwd:000

Any Idea how can I get the "adg:" position inside the string so I can later extract the number and save it as a niw field?

Regards

Re: extract a text from a string

sunny_talwar — Wed, 10 Feb 2016 15:02:55 GMT

May be this:

=Index('0x|cam:0000|adg:0000|kwd:000', 'adg:')

Re: extract a text from a string

richard — Wed, 10 Feb 2016 15:08:29 GMT

As Sunny T mentioned this is a good approach to the position of "adg:"

If you want the number behind "adg:" try:

=textbetween('0x|cam:0000|adg:0000|kwd:000','adg:','|')

Re: extract a text from a string

puttemans — Wed, 10 Feb 2016 15:36:26 GMT

Hi Lucas,

Another possibility would be using 'Subfield', in the case that your field is built always the same way as you describe.

Subfield(Tracking, '|', 3) as Y will result in adg:0000

To select the number,

right(subfield(Tracking,'|', 3), 4) should give you the 0000 (will work if there are always only 4 numbers)

Regards,

Johan

Re: extract a text from a string

Anonymous — Wed, 10 Feb 2016 17:54:44 GMT

Thanks you all for your answers. All of them are useful, but this solution was the one who fit best my needs.

Regards!

Re: extract a text from a string

sunny_talwar — Wed, 10 Feb 2016 18:16:24 GMT

Glad one of us was able to help you out

Re: extract a text from a string

Anonymous — Mon, 22 Feb 2016 14:38:25 GMT

@Richard

One more question. The textbetween worked perfectly, but didnt work for last component I wanted to extract. Probably because has no "|" at the end. This only happens in some raws. I mean, some of the raws has no "|" at the end, and others do have as they have more text before it,

In the following example Im tryng to extract the kwd info.

Example1. Works

0x|cam:0000|adg:0000|kwd:000|cid:000....

Example2 Didnt Work

0x|cam:0000|adg:0000|kwd:000

Re: extract a text from a string

puttemans — Mon, 22 Feb 2016 15:03:26 GMT

Hi Lucas,

Subfield(Tracking, '|',4) will work in both occasions.

Regards,

Johan

Re: extract a text from a string

richard — Mon, 22 Feb 2016 15:06:33 GMT

Hi Lucas,

If the code for the components can be max 4 digits you can try this:

=mid('0x|cam:0000|adg:0000|kwd:000',index('0x|cam:0000|adg:0000|kwd:000','kwd:'),'8')

As you see it's a combination of mid and index. The index function finds the starting text position of 'kwd:'