topic Re: RegExp or alternative in Connectivity & Data Prep

RegExp or alternative

Oggy172 — Wed, 19 Feb 2025 16:54:43 GMT

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

Re: RegExp or alternative

rwunderlich — Wed, 19 Feb 2025 18:11:09 GMT

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

Re: RegExp or alternative

Oggy172 — Thu, 20 Feb 2025 09:54:58 GMT

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.

Re: RegExp or alternative

marcus_sommer — Thu, 20 Feb 2025 13:54:56 GMT

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.