Regular expression

[twanqlik]

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?

[YoussefBelloum]

Hi,

take a look at this:

Regular expression [A-Z] *

you need to use macros

[sunny_talwar]

May be look here (if you have not already)

How to use regular expressions

Regular expression [A-Z] *

[sunny_talwar]

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

[twanqlik]

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?

[sunny_talwar]

Can you share what you have so far?

[twanqlik]

Yes of course, please find attached.

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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]

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.