Powershell and QlikView

    Powershell and QlikView

     

    This document will cover how to run QlikView from the command line.

     

    I will also conclude with an example of how you can use powershell to automate some tasks.

     

    The powershell commands that will be used to automate the QlikView tasks can be applied to any windows application and therefore are very useful to learn.

     

          QlikView command line

     

    Why use QlikView command line options?

     

    The best reason is that you want to automate reloading your data on a scheduled task so that when you go into QlikView you immediately have up-to-date data to look at.

     

    Reloading a QlikView document

     

    & "C:\Program Files\QlikView\qv.exe" "C:\qlikview_docs\track-my-spending.qvw" /R

     

    This is the command to run from Powershell to reload the data for the track-my-spending.qvw QlikView document.

     

    /R = Reload the data.

     

    OR

     

    /RP = Partial Reload the data.

     

    You can set variables in QlikView using the option /v.

    To set the variable MyVar to 3 add the following command to the command line:

     

    /vMyVar=3

     

    Automate Tasks in QlikView

     

    The following script example uses a great little snapin for powershell called wasp.

    You can get it from:

     

    http://wasp.codeplex.com

     

    The wasp snapin allows you to automate key strokes in any windows application.

     

    1. To use this will Powershell download the snapin and extract to a folder.

                    Within Powershell change to the folder where you extracted WASP.

    1. To find out the version of Powershell use are running type the following command and read the value of the version field: host

     

    For Powershell version 1.0:

    Run the following command from the WASP folder:

    .\Install.ps1

     

    For Powershell version 2.0:

    From within the folder that contains the WASP.dll file run the following command:

     

    1. Add-PSSnapin WASP
    2. Now you are ready to run your script using WASP commands. See the commented example below:

     

    #Start the QlikView document and give is a few seconds to start using Start-Sleep

     

    & "C:\Program Files\QlikView\qv.exe" "C:\qlikview_docs\track-my-spending.qvw"

    Start-Sleep 5

     

    #Set a variable to the current date and time

    $filename_timestamp = Get-Date -format yyyyMMddHHmmss

     

    #Select the Window that is running QlikView and send some keystrokes to the QlikView window

     

    Select-Window Qv | Select -First 1 | Send-Keys "%FX{DOWN}{ENTER}Myname#$filename_timestamp%s%{F4}%n"

     

    The keystrokes broken down are:

    %FX  - this is Alt+F then X = This takes you to the Export option of the File menu in QlikView.

     

    {DOWN} – press the down arrow key once = To ‘Export Sheet image’ option.

    {ENTER} – Select the ‘Export Sheet image’ option.

     

    Myname#$filename_timestamp – set the filename.

     

    %s – Alt+S to save the image file

     

    %{F4} – Alt+F4 to close QlikView

    %n – incase it asks to save send Alt+n

     

    As you can see you could combine the reloading of the QlikView document with this script to export an image of the document after the data was reloaded.

     

    NOTE: If you plan on using this snapin with a scheduled task you will have to add the snapin to your Powershell Profile.