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

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past few years I have been specializing in QlikView and a more user-centric form of BI. I have done numerous QlikView implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

23 Comments

  • 1
    Alan Farrell
    September 6, 2013 - 18:13 | Permalink

    Hi Barry,

    The action to run the macro to export the variables from the qvw to excel is not defined
    in the ImportExportVariables.qvw

    I am not sure if you want to change that before it’s downloaded by lots of people!!

    thanks for the tips

    Alan

  • 3
    Mathias Vanden Auweele
    September 6, 2013 - 18:22 | Permalink

    Hey Barry,
    I need to use macros in some of my scripts in order to do complex calculations on the incoming data. One of these cases is the loading of weather data where I need to calculate the wet bulb temperature from the temperature, pressure and humidity. The calculation needs an iterative process that I don’t think can be done only with the script. That’s where my macro comes in. It calculates during reload.

  • 4
    September 17, 2013 - 18:20 | Permalink

    Nice routine Barry. I use a development macro to make selected pivot table columns disappear by setting the column width to zero. I don’t even set up a button for it, I just use the Test button in the macro editor. Here’s the macro, I update it with the objectIds and column numbers I want to set.

    Sub SetCols
    SetColWidthZero “CH272”, 1
    End Sub

    REM You can’t set a pivot table column to zero width from the UI. Use this macro to force the widths.
    REM It only needs to be run once, not onOpen
    Sub SetColWidthZero (id, col) ‘Column relative to zero
    set chart = ActiveDocument.GetSheetObject(id)
    chart.SetPixWidth col, 0
    End Sub

  • 5
    October 18, 2013 - 10:50 | Permalink

    Hi Barry

    I was wondering: For some of my variables I define constraints, e.g. check of valid date for date variables, integers etc. Some even have predefined values in drop downs. Can such parameters be loaded/applied via macro (or a reload for that matter) in the same way, or is there no way around defining these in the qvw?

    Best regards
    Nicolai

    PS: Great job in Barcelona. 🙂

  • 6
    Mark
    April 9, 2014 - 20:41 | Permalink

    The Stand Alone Utility link doesn’t seem to be working. Would you be able to update that so it is available?

    P.S. This is exactly what I was just looking for. I wanted to manage variable expressions globally across multiple projects, but wanted to maintain the comments.

  • 7
    Nas
    April 15, 2014 - 10:49 | Permalink

    Hi Barry,

    I downloaded the import / export QV app but the button doesn’t seem to work.

    When I press the Import button it only opens the ‘edit module’ screen in Qlikview. The same happends when I press the export button.

    I hope you can help me out.

    Thank you in advance.

  • 8
    Josephine
    April 28, 2014 - 08:06 | Permalink

    Hi Nas,

    I face the same issue then I changed the Current Local Security to –> Allow System Access and it will work.

  • 9
    Josephine
    April 28, 2014 - 08:42 | Permalink

    Any idea whether after server reload, will the imported variables be deleted?

  • 10
    Rob Wunderlich
    May 12, 2014 - 15:27 | Permalink

    The imported variables will remain after a script reload.

  • 11
    September 19, 2014 - 04:49 | Permalink

    Thanks a Lot !!

  • 12
    Fabien
    October 13, 2014 - 11:51 | Permalink

    Thanks for sharing, extremely veryuseful!

  • 13
    Sundar
    April 21, 2015 - 06:39 | Permalink

    Your Dropbox file is not accessible anymore. It says too much generated by user and user account is blocked.
    any other route to download the utility.

  • 14
    Sohail Ansari
    June 4, 2015 - 17:19 | Permalink

    Lifesaver! Just download and used this utility and saved approx a few hours of work,

    Excellent work Barry and thank you.

  • 15
    tres
    July 2, 2015 - 07:46 | Permalink

    I wrote a section of script in reply to a community post (https://community.qlik.com/thread/170911) that would not require a macro:

    VarTable:
    Load * Inline [
    VariableName,Value
    vVariable1,1
    vVariable2,25
    vVariable4,74
    vVariable7,4
    vVariable8,55
    ];

    For i=1 to NoOfRows(‘VarTable’)
    let vName=Peek(‘VariableName’,-$(i));
    Let ‘$(vName)’=Peek(‘Value’, -$(i));
    Next i

  • 17
    Dan
    July 22, 2015 - 11:08 | Permalink

    I can seem to get how to tag documents like CVs on QV

  • 18
    Andrew Walker
    September 30, 2015 - 15:48 | Permalink

    A simple solution I use is to have tables of variables subject to a partial reload. In a development app I have a development tab with, amongst other objects not intended for the final production version, a button to open the xls file I use to store the variable definitions. In no time I can open the file, edit a variable or add a new one, save then do a quick partial reload.

    Thanks for all the materials in your blog – really really helpful.

    Cheers

    Andrew Walker

  • 19
    Alex
    August 18, 2017 - 17:27 | Permalink

    Hi Barry, Rob,

    Would you please let me know where I can download Rob’s utility
    since the link doesn’t work anymore 🙁

    Download the import/export utility
    https://dl.dropboxusercontent.com/u/6795646/QlikFix/ImportExportVariables/ImportExportVariables_Utility.qvw

    Thanks in advance.

    Regards,
    Alex

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Moving QlikView Server log files

    This is a sneak-peek at one of the topics covered in the Server Admin best practices session at the Masters...

    Close