How to RegEx in Qlik

We already rocked regular expressions in this article a while ago. We talked about regular expressions basics with few interactive examples and promises that it will all be explained from the technical point of view in the future. And since the future is now and promises should be kept, here we are.

Password evaluator

This is a very simple and common use case. E-safety is no joke but let’s be honest. How strong are your passwords? Do you use the same password in different accounts? Can you find your password in this list? Shame on you. Admins are usually aware of the lack of creativity when it comes to passwords. That’s why they specify requirements the password must fulfil. In this example we evaluate password strength based on 5 simple rules.  Write any sequence of characters into the input box, press Enter and see the result.

Password evaluator: Expressions explained

The expressions used in this example contain weird strings enclosed in “” (“.{8,}”, “[a-z]”, etc.). We already explained those in previous article (see section RegEx decomposition). Now it is time to tell you how to make Qlik understand what we mean by those senseless strings. Unfortunately, Qlik alone cannot do the job. We need to use additional language to understand regex (e.g. R, Python) and integrate it with Qlik.  We are using R’s stringr library and the str_detect() function. If you don’t know how to set up communication between R and Qlik visit here or leave a comment and we can write a separate article about this topic.

For each password requirement we have a separate expression returning 1 if a requirement is met and 0 otherwise. The ones and zeroes are handy if you want to count how many requirements are met out of the 5.

1. Password should be at least 8 characters long:
= R.ScriptAggrStr('library(stringr); sum(str_detect(q$password, ".{8,}"))', '$(vPassword)' as password)

2. Password contains at least 2 upper-case letters:
=R.ScriptAggrStr('library(stringr); sum(str_detect(q$password, "[A-Z].*[A-Z]"))', '$(vPassword)' as password)

3. Password contains at least 1 lower-case letter:
=R.ScriptAggrStr('library(stringr); sum(str_detect(q$password, "[a-z]"))', '$(vPassword)' as password)

4. Password contains at least 1 number:
=R.ScriptAggrStr('library(stringr); sum(str_detect(q$password, "\\d"))', '$(vPassword)' as password)

5. Password contains at least 1 special character !@#$&*:
=R.ScriptAggrStr('library(stringr); sum(str_detect(q$password, "[!@#$&*]"))', '$(vPassword)' as password)

Let’s go through the first expression in more detail. The other 4 are almost the same, the only thing that changes is the regular expression used in the str_detect() R function.

R.ScriptAggrStr(‘library(stringr); sum(str_detect(q$password, “.{8,}”))’, ‘$(vPassword)’ as password)

R.ScriptAggrStr(): Aggregation function that comes with the R plugin. Visit here for more information.

‘$(vPassword)’ as password: vPassword is a simple Qlik variable we use to store the password inserted into the input box. The whole expression ‘$(vPassword)’ as password is the second parameter in R.ScriptAggrStr() function (yes, you can use as within the R.ScriptAggrStr() function). We want to send the value stored in vPassword variable into R.

‘library(stringr); sum(str_detect(q$password, “.{8,}”))’: This part is the R code that is being executed. There are few things that should be noted in here:

1. Even though R works with both single (‘) and double quotes (“), use double quotes within R script called from Qlik. In this case single quotes are reserved for the R.ScriptAggrStr() function and define where the R script begins and ends.
2. When R script is executed from Qlik, data are received in R as a data frame object named q. When we need to reference a single column in a data frame, ‘$’ will do the job in R. In our case we want to reference column previously named password within table named q; therefore, q$password.
3. Each statement in R script executed from Qlik must be separated by a semicolon (;). If you are familiar with RStudio you probably don’t use semicolon at the end of each statement. That is why simple copy-and-paste of R scripts won’t work.
4. We are executing library(stringr) in each expression. This is not necessary, because library has to be loaded only once within a single R session. You can set up a sequence of R code executed with each start of the R plugin and load all needed libraries just once (can be set in SSEtoRserve.exe.config file, rserveInitScript part).

Store the result into a variable (we named it vCheckLength8) and do the same with the other 4 expressions. It is now easy to build text box with the password evaluation result on the right.

= If($(vCheckLength8)=1, chr(9989), chr(10060))&' Password length is at least 8 characters'

Hyperlinks extractor

Let’s set up the right mood for this example: your boss asked you to list all hyperlinks from a 50 page document. It is some really boring document not the fun stuff. Let’s say a contract referencing to different laws that must be read and reviewed by the legal department. Ugh! It sounds like job for a machine not for human being with much more creative potential. Moreover, the machine would be far more precise. This hyperlinks extractor could save you.

In this example we show up to 10 occurrences of string defined by a regular expression. Could be easily expanded to more rows. First, we need to create a dimension which is a simple set of numbers 1 to 10. Feel free to use this script:

Dim:
LOAD
RowNo() as #
Autogenerate 10;

We use the # field as a dimension in our table.