topic How to work Regular Expressions in Qlik Sense? in QlikView

How to work Regular Expressions in Qlik Sense?

aracelicv — Wed, 12 Apr 2017 23:08:26 GMT

Hello!

Has anybody worked with Regular Expresions in Qlik Sense?, I already know 'RegEx' does not exist in Sense, nevertheless I need to evaluate the content of diferent dimensions into my data model, for instance:

Dimension: RFC

Content: CALE891013rr08

In order to create a flag , I need to evaluate the first 4 characters are capital letters, the next 6 characters are numbers, the next 2 are lower case and the last 2 are numbers.

I´d really appreciate your help.

Thanks in advance.

Re: How to work Regular Expressions in Qlik Sense?

Anil_Babu_Samineni — Thu, 13 Apr 2017 01:11:19 GMT

What is the issue here? May be look the use RegEx

Regular expression [A-Z] *

Re: How to work Regular Expressions in Qlik Sense?

aracelicv — Thu, 13 Apr 2017 15:01:28 GMT

Hello, thanks for answering, however is not what I need.

I already work using regular expressions in Qlik View, the issue is working them in QlikSense because in Qlik Sense does not exists RegEx and I have not found how to evaluate the content of my dimensions in Sense.

Re: How to work Regular Expressions in Qlik Sense?

Anil_Babu_Samineni — Thu, 13 Apr 2017 16:37:04 GMT

True, I thought your initial group seems Qlikview. Anyway you may move this topic into Qliksense Branch

Re: How to work Regular Expressions in Qlik Sense?

aracelicv — Thu, 13 Apr 2017 16:51:34 GMT

I will, thanks for your time. Greetings

Re: How to work Regular Expressions in Qlik Sense?

mirmiranda — Sun, 16 Apr 2017 01:53:59 GMT

Hi Araceli, maybe you can try this:

SET vFirst=1;
SET vSecond=5;

Do while vFirst<=4

Table:
Load
RFC,
if(ord(mid(RFC,$(vFirst),1))>= 65 and ord(mid(RFC,$(vFirst),1))<= 90,1,0) as Prove
resident MainTable;

//ord = code ascci, hence the search is from 65 to 90
//mid = returns the part of the string, in this case, letter by letter

Do while vSecond<=10

Tabla:
Load
RFC,
if(mid(RFC,$(vSecond),1)>= 0 and mid(RFC,$(vSecond),1)<= 9,1,0) as Prove2
resident MainTable;

//here the function ord is not necesary, and the search is between 0 and 9

At the end, you get 0 if one condition is not true, for example:

RFC                            Prove      Prove2
%DOG999999XXX          0          1
MEMM444444LLL            1          1
MEMM45M678KKK         1          0

I hope I've helped you

Re: How to work Regular Expressions in Qlik Sense?

mirmiranda — Tue, 18 Apr 2017 15:56:04 GMT

Hi again Araceli.

I was thinking about your problem and my little solution too. I did many test with different cases,

and my solution it's wrong But I thought something simpler, only you need use several functions:

if(len(PurgeChar(mid(rfc,1,4),'_%&@()0123456789'))<4 or len(KeepChar(mid(rfc,5,6),'0123456789'))<6 or len(PurgeChar(mid(rfc,6,3),'_·$%&'))<3,0,1) as prove,

You get 0 if you have at leats a wrong character

Re: How to work Regular Expressions in Qlik Sense?

amonjaras — Tue, 07 Aug 2018 04:20:21 GMT

A bit late, but a RegEx connector is included in the Qlik Web Connectors package. I have tested it, and it validates the RFC as you need to.

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Data-Source-Connectors/RegEx-Connector.htm

Re: How to work Regular Expressions in Qlik Sense?

simonaubert — Mon, 26 Aug 2019 05:47:50 GMT

Hello,

You can also vote for this idea.
https://community.qlik.com/t5/Qlik-Sense-Enterprise-Ideas/Native-Regex-support-for-Qlik-Sense/idi-p/1616067
Best regards,

Simon