topic Re: Search for text in a string in QlikView

Search for text in a string

prees959 — Fri, 11 May 2018 06:27:36 GMT

Hi,

I have a lengthy string that I need to search for certain values and extract them.

For example :

a field called test results :

"ID=9879867234, Confidence=90, Desc=Non destructive testing part2 (KPP=081678402 KPP-8) UNIT Area: NORTH EAST SECTOR (077)"

"ID=1584858834, Confidence=80, Desc=Dye Examination (KPP=998478111 KPP-9) UNIT Area: AAB (5)"

Ideally I need to create some fields from these strings. In the above examples :

Confidence : 90

KPP : 081678402

UNIT Area : NORTH EAST SECTOR (077)

Confidence : 80

KPP : 998478111

UNIT Area : AAB (5)

Can anyone help please?

Many thanks,

Phil

Re: Search for text in a string

datanibbler — Fri, 11 May 2018 06:38:45 GMT

Hi,

use the INDEX() function to find where the nth (probably 1st?) instance of your searched-for piece begins within that string (better use a TRIM() around it all to avoid leading and trailing spaces) and then use the MID() and LEN() functions to cut out just the piece you want.

Best regards,

DataNibbler

Re: Search for text in a string

sasiparupudi1 — Fri, 11 May 2018 11:58:05 GMT

Try

Load

TextBetween(x,'Confidence=',',') As Confidence,

Trim(TextBetween(x,'KPP=','KPP-')) As KPP,

Trim(SubField(x,':',-1)) As [UNIT Area]

Inline

[

ID=9879867234, Confidence=90, Desc=Non destructive testing part2 (KPP=081678402 KPP-8) UNIT Area: NORTH EAST SECTOR (077)

ID=1584858834, Confidence=80, Desc=Dye Examination (KPP=998478111 KPP-9) UNIT Area: AAB (5)

](delimiter is '|');

Re: Search for text in a string

prees959 — Fri, 11 May 2018 13:13:50 GMT

Perfect - Thank you!