Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Sorry, there are no regular expressions on QlikView, check this post http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/
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
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.