Importing and exporting variables

By Barry Harmsen

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.

Macro’s?

First off, I want to say that using macro’s in user-facing applications, especially those deployed via QV Server,  is often a bad idea. That being said, macro’s can be very useful to speed up tasks during the development of your QlikView applications. In this case, I have created two macro’s that I use to import and export my variables between QlikView and an external Excel file:

  • ImportVariablesFromExcel: imports all variables from the Excel file specified in the v.Filename.Variables variable. The path used should be relative to the QlikView application.
  • ExportVariablesToExcel: exports all current variables, except for the QlikView specific ones, to a new Excel file. Doesn’t automatically save so you can verify the output before overwriting the existing file.

Both these macro’s read and write in the same Excel format, a worksheet called Variables containing 3 columns: Variable, Expression and Comment.

Next, I use two buttons on my Developer sheet (a standard sheet that I use during development to quickly verify the integrity of my app and troubleshoot various issues) to trigger these macro’s. And voila, no more copy & pasting or reloading to retrieve new and modified variables.

You can download an example app that demonstrates these macro’s by clicking below. Please be aware that these macro’s require System Access as they access external Excel files.

Download the example application and code

Do you use any macro’s to speed up development? If so, I’d be very interested in hearing about them, feel free to comment below.

If you enjoy these posts, you may be interested in the Masters Summit for QlikView, a 3 day event designed to take your QlikView skills to the next level with practical, hands-on sessions on Data Modeling, Scripting, Expressions, Visualization and much more. I’ll be speaking there alongside QlikView legends Bill Lay, Oleg Troyansky and Rob Wunderlich. An excellent opportunity to acquire new skills and connections.

 

Update 2013/12/04:

TheHat

Rob Wunderlich has updated my application so that you no longer have to copy and paste macro code into your application. These macro’s can now be used as a stand-alone utility. Very helpful to have in your developer toolbox. Thanks Rob!

Download the import/export utility