Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
I don't think it is a macro... I think it is a user created function
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?
Can you share what you have so far?
Yes of course, please find attached.
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);
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);
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.