topic Re: String extraction in QlikView

String extraction

EDehzn — Wed, 26 Jan 2022 09:37:50 GMT

Hi,

I am trying to extract the code which starts with 'PA9' from a string in column OTHERS as below and make a new column/field for the code.

OTHERS

~PA9235 testing for qlik

PA92222 testing

test123~PA92325~test

due to the unfixed code length and location, i am unable to use subfield() for this.

Is there any other way for this?

Re: String extraction

MarcoWedel — Wed, 26 Jan 2022 11:27:45 GMT

assuming the code consists of only 'PA9' and the following numeric characters, i.e. it ends with any non-numeric character, maybe you could use

If(Index(OTHERS,'PA9'),'PA9'&Left(SubField(OTHERS,'PA9',2),Index(SubField(OTHERS,'PA9',2),PurgeChar(SubField(OTHERS,'PA9',2),'0123456789'))-1)) as Code

hope this helps

Marco

Re: String extraction

EDehzn — Thu, 27 Jan 2022 01:04:15 GMT

Hi Marco,

This works well but there were still codes which were not extracted correctly in some case such as:

qlik~PA92233 No.12445

test PA92222 /test

Re: String extraction

MarcoWedel — Wed, 26 Jan 2022 17:33:22 GMT

so not all the codes of interest actually start with 'PA9' like you specified ... ?

Re: String extraction

EDehzn — Thu, 27 Jan 2022 07:05:15 GMT

Hi Marco,

Sorry for the confusion. I have edited the codes. These codes do start from PA9, however some of the extraction failed when the codes ends like these:

qlik~PA92233 No.12445

test PA92222 /test

Re: String extraction

MarcoWedel — Thu, 27 Jan 2022 18:11:18 GMT

The expression seems to work with these values as well.

Can you post an example application that demonstrates "OTHERS" values not working with this expression?

Thanks

table1: LOAD *, If(Index(OTHERS,'PA9'),'PA9'&Left(SubField(OTHERS,'PA9',2),Index(SubField(OTHERS,'PA9',2),PurgeChar(SubField(OTHERS,'PA9',2),'0123456789'))-1)) as Code Inline [ OTHERS ~PA9235 testing for qlik PA92222 testing test123~PA92325~test some string without code qlik~PA92233 No.12445 test PA92222 /test ];

Re: String extraction

EDehzn — Tue, 01 Feb 2022 13:59:37 GMT

Hi Marco, here are some examples where the code have some issues:

PA92124 /HEG/0000 test

PA932145 /RFG/4567 qwrty

PA99921 BILL NO 1234 3456 4

Apologies for the dummy data but i can assure u that the string sequence and type is the same

Re: String extraction

MarcoWedel — Tue, 01 Feb 2022 16:49:54 GMT

Hi,

I didn't notice I used a wrong function here at one point.

Can you try

If(Index(OTHERS,'PA9'),'PA9'&Left(SubField(OTHERS,'PA9',2),FindOneOf(SubField(OTHERS,'PA9',2),PurgeChar(SubField(OTHERS,'PA9',2),'0123456789'))-1)) as Code

instead?

Re: String extraction

cwolf — Tue, 01 Feb 2022 21:43:31 GMT

The easiest way is a JScript function using a regular expression:

Macro:

function GetPA(s){ var m = s.match(/PA9\d*/); return m==null ? "" : m[0]; }

Script:

load Text, GetPA(Text) as PA Inline [ Text ~PA9235 testing for qlik PA92222 testing test123~PA92325~test some string without code qlik~PA92233 No.12445 test PA92222 /test PA92124 /HEG/0000 test PA932145 /RFG/4567 qwrty PA99921 BILL NO 1234 3456 4 PA89921 BILL NO 1234 3456 4 JSriptPA97264018Macro PAPPA95547P911236 ];

Result:

- Christian

Re: String extraction

MarcoWedel — Wed, 02 Feb 2022 17:45:51 GMT

same with VBS RegEx:

Function RegExFind(str) set RE = New RegExp RE.Pattern = "(PA9\d*)" RegExFind = RE.Execute(str)(0).SubMatches(0) End Function

table1: LOAD *, RegExFind(OTHERS) as Code Inline [ OTHERS ~PA9235 testing for qlik PA92222 testing test123~PA92325~test some string without code qlik~PA92233 No.12445 test PA92222 /test PA92124 /HEG/0000 test PA932145 /RFG/4567 qwrty PA99921 BILL NO 1234 3456 4 ];

hope this helps

Marco