Category Archives: Script

A very elegant link table script

Some nice out of the box thinkingToday was the last day of the Masters Summit for Qlik in Johannesburg and we all had a great time. One of the things I very much enjoy about these events is the chance to speak with so many experienced people who are enthusiastic about Qlik and keen to share their knowledge. I always go home with some cool new tricks and insights.

What I want to share with you today is an alternative script for building a link table. This was shared with me by Rene McGregor and the nice folks over at QlikView South Africa (who have also been very supportive in bringing the Masters Summit to South Africa, much appreciated).

read more »

Remote include scripts

Got a bunch of scripts here for you, where do you want me to put them?One of the reasons why I like QlikView so much is that there are always new functions and applications to discover. Yesterday I was at a client who is a Qlik OEM partner. During lunch we discussed various uses of include scripts. The question came up if it was possible to remotely update include scripts. At the same time they didn’t want these external scripts to be viewable (they sell a solution based on QlikView, clients get the solution but the OEM owns the code). Of course, that makes things a little more complicated. The include script sits outside of QlikView in plain text, so anyone can read it. Besides that, updating an external script that sits on the client’s local server is also not a straightforward affair.

I started wondering if it might be possible to fetch an include script from a remote website. So I just tried it, and it works!* (in QlikView, Sense requires Legacy Mode to be enabled, I will let you decide if that is a good idea)

Try this one for example:

$(must_include=http://www.qlikfix.com/remote_include/?license=12345);

In my opinion, this opens up some interesting use cases:

read more »

Aliases, to indent or not?

“Code is read more often than it is written”

  – common programming adage

As someone who often has to look at/modify QlikView script written by others, I have come to appreciate the value of readable script. Rather than taking an “if it works, it works” attitude, I always try to write my script with readability and consistency in mind. Somewhere down the line someone else (or worse, you) might have to decipher what a specific script is doing in order to modify or extend it.

Today I want to ask your opinion on the readability of a specific part of the QlikView script, indentation of aliases within LOAD statements. To this end, I’ve taken a piece of script from the QlikView System Monitor and formatted the aliases in three different ways. (note: I don’t agree with the absence of white space in functions and between operators, but have left that as is)

Have a look at the three options, and then please answer the two multiple choice questions below.

read more »

Generating random test data in QlikView with Mockaroo

Functions to create random data in QlikView


I often need random data. For example to use in an example file on this blog, to deliver a customer demo or just to test out something new. As I am probably not the only one with this need, here’s a short and sweet post on how to generate random data for use in QlikView.

read more »

Scripts and wizards, too much script, not enough wizards

ScriptVsWizardsI recently read an interesting post by James Richardson over at the Business Discovery Blog: Wizards vs Scripts. In the post James makes the case that QlikView scripting is not old-fashioned or too hard, but is evidence of the power of QlikView as a platform.

Let me first state that I love QlikView scripting. I’m a guy who writes script for fun. I also agree that scripting offers much more flexibility than a visual solution ever could. With those things in mind, I would like to present a different viewpoint: I think that QlikView places too much emphasis on scripting. In my opinion, the default approach should be much more visual.

read more »

Importing and exporting variables

Importing and exporting variables in QlikViewWhen using variables in QlikView, a common best practice is to store them outside your QlikView application, or to define them in the script. However, there’s one thing about this approach that often annoys me. When defining a new variable during development, you either have to:

  • define the variable twice: once in the variable editor and then again in your external file/script to ensure that it is persisted;
  • or, define it once in your external file/script and then reload the application to make it available in the front-end, potentially leading to a lot of unnecessary downtime.

Fortunately, there is a solution that lets you define your variables once in an external file -and- have it available in the front-end (almost) immediately without reloading. read more »

Masters Summit for QlikView: European Edition

Masters Summit for QlikViewAfter the success of the Las Vegas edition last April, I’m excited to be once again presenting alongside Rob Wunderlich, Bill Lay and Oleg Troyansky at the European edition of the Masters Summit for QlikView.

Masters Summit for QlikView; London and BarcelonaComing to London from October 9  to 11 and to Barcelona from 14 to 16 October, the Masters Summit brings you 3 days of hands on sessions where we will discuss advanced techniques in building complex solutions with QlikView. The goal of this event is to take your QlikView skills to the next level and help you become a QlikView  master.

For the early birds, there is an attractive discount of US$ 300 (around 225 Euro’s) until August 16th, which, for example, should be enough to cover air fare from most locations within Europe. Make sure you do not miss out on this great offer and:

Register for the Masters Summit for QlikView

I hope to see you all there!

DISTINCT can be deceiving

DISTINCT can be deceiving

Never think you know it all! Today I have for you a little ‘gotcha!’ about the LOAD DISTINCT statement that I picked up from Oleg Troyansky at the Masters Summit for QlikView.

Common knowledge about LOAD DISTINCT is that it will remove duplicate records from the input table, similar to how the DISTINCT clause works in SQL. For example:

Regular LOAD DISTINCT

However, when used in combination with the JOIN or CONCATENATE statement, the results can be surprising, and even counterintuitive.

read more »

WHEN and UNLESS

Mr. ComplainypantsNo, this post isn’t about that negative co-worker or friend who’s always second-guessing your ideas and solutions, it’s about two QlikView script statements that I believe deserve a little more attention: WHEN and UNLESS. (Inspired by Steve Dark’s post on Hidden QlikView Features)

Both statements can be used as an easier, shorter version of the IF-THEN-END IF statement. For example, to set the default value for a variable, but only if it hasn’t been set already:

WHEN ‘$(Variable)’ = ” SET Variable = ‘Value’;

Or, another example, when you’re in a loop and want to add a separator to a concatenated value, unless it’s the last iteration of the loop:

UNLESS $(i) = $(ForMax) LET Variable = ‘$(Variable);’;

One thing you’ll immediately notice when you use these statements is that QlikView syntax highlighting doesn’t seem to recognize them. Don’t worry though, as the attached example application demonstrates, they do really work.

Download the example application

Visual FX in QlikView (4): Season’s greetings!

Christmas time is coming near and I’m in a festive mood, so today I have a short post to wish you all a Merry Christmas and a Happy New Year!

Of course, it wouldn’t be a QlikFix Christmas if I hadn’t whipped up a little visual effect in QlikView. Without further ado, here is my QlikView Christmas card to you:

But wait, there’s more! Inspired by the Christmas theme over at Matt Fryer’s QlikView Addict blog (a recommended read, by the way), I decided to create a small document extension that lets you add a little Christmas spirit to your own QlikView documents. Amaze (or annoy) your clients, co-workers and users! For example, how about adding a little snow to the golf course?
read more »