Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

cxclucas
New Contributor III

extract a text from a string

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

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: extract a text from a string

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:','|')

View solution in original post

8 Replies

Re: extract a text from a string

May be this:

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


Capture.PNG

Partner
Partner

Re: extract a text from a string

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:','|')

View solution in original post

puttemans
Valued Contributor

Re: extract a text from a string

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

cxclucas
New Contributor III

Re: extract a text from a string

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

Glad one of us was able to help you out

cxclucas
New Contributor III

Re: extract a text from a string

@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

Highlighted
puttemans
Valued Contributor

Re: extract a text from a string

Hi Lucas,

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

Regards,

Johan

Partner
Partner

Re: extract a text from a string

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:'