Qlikview Match

This post demonstrates the Qlikview Match Function, which is Qlik’s equivalent to the IN operator in TSQL.

Just the other day I was working with a large dataset and decided to filter down the data in the Qlikview Load Script.  I was working with a Zip Code table, linking it out to a larger dataset with geographic location data.  In SQL, I used the IN operator to specify which Zip Codes I wanted to SELECT.  A simple query does the trick:

However, when trying to apply this simple WHERE clause in a Qlik Load Script, it will not work.  This is where the Qlikview Match function comes into play.  Here are a few examples of how to use the Qlikview Match function, as well as similar functions called MixMatch and WildMatch.

Keep in mind this is for demonstration purposes.  There are many different ways you could achieve the same result.  For these examples, I have loaded a Zip Code table into Qlikview, which consists of 42522 lines.  When viewing the contents of New York State in a Table Box, there are many results:

Qlikview Match

Let’s say I only want to see Zip Codes 14221, 14086 and 14203 in my Qlikview application.  I can modify the Load Script as follows:

Following the Reload, my Table Box looks like this, narrowed down to just the three Zip Codes I wanted:

Qlikview Match

WildMatch

Using the WildMatch function, I can Load only Cities where the first four characters are BUFF (case-sensitive)

Results in:

Qlikview WildMatch

MixMatch

Lastly, using the MixMatch function, I can Load only Cities where the text equals buffalo (NOT case-sensitive).  So, if I were to run the following, it would come up with ZERO records because the search string ‘buffalo’ does not match the fact table data, where the city is ‘BUFFALO’

However, if I use MixMatch instead:

I do get results:

Qlikview MixMatch

Additional Content

In addition to the post above you can visit my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

Leave a Comment