Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

twanqlik
New Contributor III

Regular expression

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:


Regular expression.PNG

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?

1 Solution

Accepted Solutions

Re: Regular expression

This is assuming that the ticket number is always like this

xxx-xxx-xxxxx

or

#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);

9 Replies
YoussefBelloum
Esteemed Contributor

Re: Regular expression

Hi,

take a look at this:

Regular expression [A-Z] *

you need to use macros

Re: Regular expression

May be look here (if you have not already)

How to use regular expressions

Regular expression [A-Z] *

Re: Regular expression

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

twanqlik
New Contributor III

Re: Regular expression

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

Can you share what you have so far?

twanqlik
New Contributor III

Re: Regular expression

Yes of course, please find attached.

Re: Regular expression

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);

Capture.PNG

Re: Regular expression

This is assuming that the ticket number is always like this

xxx-xxx-xxxxx

or

#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);

twanqlik
New Contributor III

Re: Regular expression

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.

Community Browser