topic Re: Regular expressions for parsing strings in the QlikView script? in QlikView

Regular expressions for parsing strings in the QlikView script?

Wed, 16 Jan 2013 17:38:11 GMT

Hey guys. Is there a smart way to parse strings in a QV script a la regular expressions? I have a string in my data and I need to parse out a portion of it, but I can't find any string function that can tell me whether an indexed character is a number or letter. The string should always start with 6 digits, so I need to throw away any strings that contain alpha chars in the first 6 places. Then, the 7th character should be a letter U, so I want to throw away any strings that don't contain 6 digits followed by a letter U. Then, the 8th character must be a number. The 9th character could be a number or letter, but I only want it if it's a number. Anything after the 9th character I want to discard.

So for example, if the string is 092962U1, the result of the expression should be U1. If the string is 080738U1EP, the result should be U1. If it's 060645U22, the result should be U22, and if the string is 060645U22JP, the result should be U22. Does that make sense?

I appreciate the help.

Regards,

Eric

Re: Regular expressions for parsing strings in the QlikView script?

danielrozental — Wed, 16 Jan 2013 17:42:13 GMT

Sorry, there are no regular expressions on QlikView, check this post http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

Re: Regular expressions for parsing strings in the QlikView script?

vgutkovsky — Wed, 16 Jan 2013 22:12:24 GMT

Here's a simple way to do this, assuming your field is called myField:

if(mid(myField,7,1)='U' and isnum(num#(left(myField,6))),

mid(myField,index(myField,'U'),if(isnum(num#(mid(myField,9,1))),3,2)),

'garbage' //7th character is not 'U' and/or first 6 characters are not numbers

)

Regards,

Vlad

Re: Regular expressions for parsing strings in the QlikView script?

chrisbrain — Thu, 17 Apr 2014 08:18:11 GMT

Just a quick note for QVSource users, we have added a new RegEx option in our text analytics and sentiment analysis connector.

Currently it supports finding strings in a supplied text (for example searching for all URL or phone numbers etc.) but we are keen to get feedback and add other features which will be commonly needed by QlikView users.