topic Re: Regular expression in QlikView

Regular expression

twanqlik — Wed, 08 Nov 2017 15:16:43 GMT

I build a regular expression that I want to use in Qlikview

Regular expression:

^(?P<sector>CL|LI|HC|CRS|GBL|TEST)?\s*(?P<country>[A-Z]{2}|MULTI|TEST)?\s*(?P<date>\d{8})?\s*(?P<bgcat>[A-Z]{4,6})?\s*(?P<asset>[A-Z]{2})?\s*(?P<type>[A-Z]{4,5})?\s*(?P<subtype>(Cross sell|Test drivers|[A-Za-z&]+))?\s*(?P<name>.*?)?\s*(?P<ticket>#?[a-zA-Z]{3}-\d{3}-\d{5})?$

For names like this, example name:

CL RU 20161017 CRBCRC CA CLCP Welcome #LGJ-309-18486

When i run this regular expression on https://regex101.com/ it will result in a split like on the below image:

My goal is to use this expression in Qlikview to store the matched values in different fields.

E.g. store the 'CL' value in the Group Sector field

Store the 'RU' value in the country field

Etc.

However, can Qlikview handle regular expressions, i couldn't find how to use them and in case not, what is then possible?

Re: Regular expression

YoussefBelloum — Wed, 08 Nov 2017 15:19:18 GMT

Hi,

take a look at this:

Regular expression [A-Z] *

you need to use macros

Re: Regular expression

sunny_talwar — Wed, 08 Nov 2017 15:20:27 GMT

May be look here (if you have not already)

How to use regular expressions

Regular expression [A-Z] *

Re: Regular expression

sunny_talwar — Wed, 08 Nov 2017 15:21:03 GMT

I don't think it is a macro... I think it is a user created function

Re: Regular expression

twanqlik — Thu, 09 Nov 2017 13:10:40 GMT

Based on the complexity of writing a regular expressions with the functions as shared i decided to try another way.

With using a combination of Subfield, len and findoneof I'm almost there.

I'm only struggling with one part, maybe you have an idea about this.

Input

CL RU 20161017 CRBCRC CA CLCP Welcome #LGJ-309-18486

Output (ticketnr)

#LGJ-309-18486

Input

CL BG 20161121 CRBCRC CA CLCP Birthday Campaign

Output (ticketnr)

BLANK

Hence, I want to extract the ticketnr from the name in case there is a ticketnr.

A ticketnr has always 2 delimiters 2 x -

Example ticketnr's:

#LGJ-309-18486

LGJ-309-18486

Any idea how to do this?

Re: Regular expression

sunny_talwar — Thu, 09 Nov 2017 13:12:03 GMT

Can you share what you have so far?

Re: Regular expression

twanqlik — Thu, 09 Nov 2017 13:15:18 GMT

Yes of course, please find attached.

Re: Regular expression

sunny_talwar — Thu, 09 Nov 2017 13:26:31 GMT

Not sure if this completely meet your need but see this to get ticket

LOAD Name as Name_old,

Trim(Mid(Name, Index(Name, '-')-4)) as Tickect

FROM

[Name.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Regular expression

sunny_talwar — Thu, 09 Nov 2017 13:29:13 GMT

This is assuming that the ticket number is always like this

xxx-xxx-xxxxx

#xxx-xxx-xxxxx

and it is always around the end... We can relax this condition if the first condition is still true, by using this

LOAD Name as Name_old,

Trim(Mid(Name, Index(Name, '-')-4, 14)) as Tickect

FROM

[Name.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Regular expression

twanqlik — Thu, 09 Nov 2017 15:19:27 GMT

Thank you very much, this did help me to finalize the script for this section.

I've uploaded my file, maybe it can also help other developers in the future.

When you are interested, could you please have a look at my script, maybe you have some ideas to improve the script for better performance.