Regular expressions in the load script

By Barry Harmsen

Regular expressions (or “regex”) are a developer’s best friend, they offer an extremely powerful way to search for or replace patterns in strings of text.

This post assumes that you already know how what regular expressions are and how to construct them. If this is not the case, then you may want to read up a little bit here before proceeding. Trust me, if you regularly have to deal with validating and cleaning input data then regular expressions are well worth your time.

Data validation and transformation using regular expressions, download the example application

Unfortunately, QlikView does not (yet*) natively support regex, however, there is a way to use regular expressions in your QlikView load script: VBScript.

Using VBScript, we can create functions that add regex functionality to our load scripts. There is just one caveat; since VBScript functions are processed slower than QlikView’s built-in functions, you should always do thorough performance testing before deploying the application. (Of course, you should always test before deploying any application, I just want to stress the point.)

So, how can VBScript regex functions be used in a QlikView load script? The accompanying example qvw shows how it’s done. In the example application I have defined three VBScript functions that can be used to add regex functionality to your load scripts:

  • RegExTest: test if (part of) an input string matches a defined pattern. This is a very useful function to perform validation, for example to check if an input field contains a valid email address.
  • RegExReplace: replace (part of) an input string that matches the specified pattern with another string. This function can be used to reformat input data, for example to apply the same format to all telephone numbers.
  • RegExFind: Extract (multiple parts of) a string that matches the defined pattern. When multiple matches are found a concatenated string is returned. This could be used to extract all email addresses from a piece of text, or to extract all external links from an html file.

Download the example qvw containing the functions and examples of how to apply them. If you  have any questions then feel free to ask them in the comments section.

* Of course, the best option would be for QlikTech to include regular expressions as a built-in function. For those of you with access to Q-Force, please support the product enhancement request for regular expressions by going here.