Friday 27 February 2015

Populate QlikView variables from an external file

When building multiple tiered QlikView solutions, it is often handy to be able to define global variables that are then used across all QVWs within the solution. For example, having a single variable that defines where QVDs are to be stored and loaded from, can make moving the app from a development environment to a production server a little quicker and simpler.

I'm starting to feel like a broken record saying this, but there are of course multiple ways to achieve this in QlikView. One method, and probably the most commonly used, would be to define the variables in a QlikView script file as follows:

SET vQVDPath = C:\QlikView\Data\QVDs\;

The script file can then be included in multiple QVWs by added the following line towards the start of each script:

$(Include=..\config\variables.qvs);

This method is perfect, with one exception, to edit the variables a user would need at least some basic QlikView scripting experience. What if we could store the variables in a simple table, such as an Excel spreadsheet, and then load them into multiple QVWs from there. This would allow anyone to edit the spreadsheet and thus change where the QVDs are to be stored.

The method I'll show you below uses the QlikView Components (QVC) script library. If you are not familiar with QVC I highly recommend you take a look here. It provides a series of subroutines and functions intended to assist and speed up the development of QlikView applications. One of these subroutines, Qvc.PopulateVariables, does exactly what we require.

The first thing we need to do is to include the QVC script file into the application in the same way we included the script file above:

$(Include=..\QVC\Qvc_Runtime\Qvc.qvs);

Next we load the variables into a table from the spreadsheet where they have been entered.

Variables:
LOAD
    Name,
    Definition
FROM [..\config\variables.xls]
(biff, embedded labels, table is Sheet1$);

Whilst this example uses an Excel spreadsheet, the table can be loaded from any source such as an XML file, a CSV, a database, etc. What is important is that the table contains at least 2 fields, that the first contains the names of the variables to be created, and the second contains the values to be set to them. Once this table exists, you can call the Qvc.PopulateVariables routine to convert the table into variables.

CALL Qvc.PopulateVariables ('Variables', -1);

The first parameter is required and should be a string containing the name of the previously loaded table which contains the variable definitions. The second parameter is optional and defines whether the variables should be defined using SET (0) or LET (-1). With that complete, we can of course drop the variables table as it is now surplus to requirements.

DROP TABLE Variables;

We now have all our variables set in the application as we intended and can perform the same script snippet in each QVW where we need the same variables to be set.

One final trick, what if you wanted to load variables from another QVW file? Thankfully this can also be done. You might not be aware that a QVW file includes an XML footer. This XML contains many useful bits of information, including a list of the variables within the app and their values when the QVW was last saved. The following script can be used to load these variables from the XML of a QVW into a table.

Variables:
LOAD
    Name,
    RawValue
FROM [MyApp.qvw]
(XmlSimple, Table is [DocumentSummary/VariableDescription]);

Once in a table, you can used the same QVC call as above to populate them into variables,

3 comments:

  1. Here's an extension to that idea. My firm has a lot of turnover, so the identities of sales, customer service rep (CSR), admin staff, and others change frequently. Each employee gets a 4 letter identifier (I am KBER). I have many internal reports where they want all the CSR's or all the Sales people etc included in the report. At first, I hardcoded them into if statements as calculated dimensions (e.g. If(mixmatch(SalesID,'TOMM','DICK','HARE')>=1,SalesID,Null()) and then selected "Suppress if Null", which provided a table with just the current sales people. However, as you might imagine, keeping these up to date is tedious and fraught with error. I now use the following technique:

    We keep an Excel file called "User Groups" in my "IncludeFiles" directory. The Excel report has separate tabs, such as US Sales, Canada Sales, CSR, etc. Each tab is simply a group name and
    member list, where row 1 is the header (usually A: Ugroup and B: member), and the following rows are e.g. US Sales, TOMM, US Sales, DICK, US Sales, Hare, etc.

    Then, I use some code we stole from soneone (Rob Wunderlich?):
    USSalesReps:
    LOAD Ugroup,
    Member
    FROM
    [R:\IncludeFiles\GM User Groups.xlsx]
    (ooxml, embedded labels, table is [US Sales]);

    SET vUSSales=; // Initialize variable
    SET comma=; // Initialize variable
    SET esc = chr(01); // The character we will use to escape quotes

    // Loop through the rows of the table to build the expression string,
    FOR i = 0 to NoOfRows('USSalesReps')-1
    LET vUSSales = vUSSales & comma & $(esc) & peek('Member', i, 'USSalesReps') & $(esc);
    SET comma = ','; // Next time through the loop, insert a comma
    NEXT i
    LET vUSSales = replace(vUSSales, $(esc), chr(39));
    LET vUSRepCnt = i;
    DROP TABLE USSalesReps;

    What this does is build a string variable "vUSSales" that looks like this: 'TOMM','DICK','HARE'

    I then change my formula to if(mixmatch(SalesID,$(vUSSales))>=1,SalesID,Null()) and life got very
    easy. The admin staff can go into the UserGroup file at any time, update the members, and save the file. That night, when reports are run, the up to date groups are automatically used. (Note the use of $-sign-expansion - that IS required!)

    There are a lot of uses for this technique. For example, we use nPrinting, and changing holiday parameters was a pain, until I simply put the holiday dates into a tab in the UserGroup excel file, and used the same method to create a vHolidays variable that now goes into all my daily reports, so that reports aren't generated for days when no one is working -> firstworkdate(today(1)-1,1,$(vHolidays))

    This is a huge timesaver for me; hope it helps others as well.

    ReplyDelete
  2. Unless I'm missing some complexity, it seems like it would have been much easier to create your spreadsheet with three columns: Name, ID, Functional Group (where Functional Group is Sales, CSR, Admin, etc.) then simply load the spreadsheet into a Qlik table. By doing it this way you can then use the power of Qlikview, such as List Box to select functional group, or use functional group in set analysis:

    sum({} Amount),

    or with an if statement in calculated dimension:

    if(functional group = 'Sales', ID)

    ReplyDelete
  3. Hi Team,

    Could you please advise how can i restrict my .qvw file not to be used in any binary load.

    ex. I have created a .qvw file and i don't want it to be used in binary load by anyone, please Advise.

    thanks,
    Shiv

    ReplyDelete