RegExp or alternative

[Oggy172]

Hi all,

I am doing some work in QV, and need to pull a 7 digit number from a string.

I had a look around and there's references to two solutions, neither of which seem to work.

Solution1 - 

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

Even running the sample RegEx.qvw provided on the URL, I get an error 

I get a very similar error when used in my app.

I also came across this Javascript variant  - Solution 2

Solved: String extraction - Qlik Community - 1885442

Editing it to 

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

This fails.

Here is the section of my load script

ReturnsDetail:
load
GetRegExp ([Advice Note No]) as RegexTest,
[Stock Code],
[Expiry Date], 
sum([Previous Qty])	as Cases,
count(distinct [Container Id]) as Plt,
'Oggy' as [Source]
from 
'$(FoundFile)'
(ooxml, embedded labels, table is [DETAIL])
GROUP BY [Stock Code], [Expiry Date], GetRegExp ([Advice Note No]);

Which errors on "Invalid Expression"

I noted the table name is asterixed out, but if I remove the GetRegExp line, it rund through fine.

Ideally I'd like to get RegExp working, but, is there another way of searching for a 7 digit consecutive number in a variable length field? 

Thanks

[rwunderlich · Accepted solution]

You can use RegEx as shown in QlikView Macro example. It's just that the example script is a bit out of sync with the Macro example. The Macro Functions are expecting a third parameter to indicate case sensitive match. You can modify the script example lines to provide the third parm:

if(RegExTest([Phone number], '^\d{3}-\d{4}', False())

or modify the macro. 

Here's another example of QV Regex:
https://qlikviewcookbook.com/recipes/download-info/regular-expression-pattern-matching/

-Rob

[Oggy172]

Thanks @rwunderlich 

I can get that working now, which is a relief, though it doesn't appear to work in a "group by" load function.

I've just used a staging table to get around this.

[marcus_sommer]

For all who are quite comfortable with the regex-syntax and mainly be able just to write them down without much efforts or investigating it's surely a convinient way to extract/check sub-strings.

But regex is seldom mandatory within Qlik because there are a lot of powerful string- and conversion-functions and the shown transformations are not very difficult to get within Qlik, for example using date#() to convert a date-string. This means there are inbuilt alternatives to a regex - not so nicely but be working.

If I remember correctly I used already transformations within a group by - you may move this part to a preceding- or a resident-load.