Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prees959
Creator II
Creator II

Search for text in a string

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


1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Try

Load

x,

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

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

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

Inline

[

x

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 '|');

View solution in original post

3 Replies
datanibbler
Champion
Champion

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

sasiparupudi1
Master III
Master III

Try

Load

x,

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

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

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

Inline

[

x

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 '|');

prees959
Creator II
Creator II
Author

Perfect - Thank you!