Today I will show you how to store variables and their definitions in an external file… and why you would want to do that.

To start with the last part, in my opinion it is important to apply calculations and business rules consistently across QlikView applications. Doing this manually takes a lot of time and is prone to errors. Besides that, it is just not a very fun task. Fortunately, a lot of this can be handled by setting up a multi tiered environment with one or more QVD layers (for more information on this, see Rob Wunderlich’s excellent “Understanding & Best Practices with QVD files” webinar and slides).

Sometimes however, you need to use calculations that cannot be (easily) pushed to a QVD layer, for example when calculating averages or using set analysis. How can you store those calculations in a central place so they can be reused?

The first step is to create an external file (or database) to store your variable definitions. In this example I have created an Excel file with 3 columns, Name, Expression and Comment.

In the next step, the data is loaded into QlikView as a table and a little bit of script loops through all the rows and creates the variables.

And that is all there is to it! The variables that were defined in the Excel file have now been created in the front-end, ready the be used. An example QVW with the full script can be downloaded below.

Download the example QVW and source data

The only downside to this method is that the comments are not being written to the the comment section of the variable, but are instead stored together with the expression. Fortunately, I will be putting up a guest post that deals with this very subject in the near future, so stay tuned!

As always, if you have any comments or questions, please post them below.