Variables With Parameters In Qlik Load Scripts

The implementation of variables and dollar sign expansion in QlikView and Qlik Sense is incredibly flexible and can be used to great effect in load scripts. In this post I show how and give a recipe for a NetworkDays function that excludes public holidays.

Background

The ability to pass parameters into variables is something I have been using for some time, after picking up the technique from Stephen Redmond’s blog.

These variables can make code reuse even more flexible, which is great for pivot style tables where there is a fixed number of columns (perhaps 12 months) and more than one total column (perhaps a Total and an Average).

As I suspect this usage of parameterized variables has been covered elsewhere before I will not repeat it here. However, the usage of similar variables in the load script is another way they can be utilised, to clean up script and remove repetition.

Repetition, Repetition, Repetition

There are some tasks that you can end up doing many times over in a load script, some of these can make code look untidy and mean that a subtle change in requirement can require a large number of changes to the load script. Variables can help here.

For example, a common requirement is to format dates. When loading from a text file this needs to be done twice for each date – once to convert it from a string to a number and then again to format it as a date. The code to do this can be placed into a variable like this:

You will note that the parameter into the variable is placed where the $1 appears in the variable code. The $1 can be repeated, allowing the same text to be inserted multiple times. Additional parameters can be passed in using a comma separated list, subsequent parameters are referenced by $2 and $3 etc.. You can see how this can become quite powerful?

Also with text files (and sometimes poorly defined databases) instead of nulls empty strings can be returned, or even the text NULL. These can be replaced out with an IF statement, and if you have many in one file a variable can be employed:

set vSetNull = if($1 = '', null(), '$1');

The variable is then used in the script like this:

LOAD
$(vSetNull(SomeField)) as [Some Field],
...

Similar variables could be used to replace null values with ‘Missing’ or 1/1/1900 with null.

Hopefully now you have a good idea how these variables work, so we can move on to a more advanced example.

Excluding Bank Holidays From NetworkDays

The NetworkDays function is a useful function that takes two parameters and then returns the number of Monday-Fridays between them. One thing it doesn’t do though (for obvious reasons) is to take into account the various different public holidays in different regions. These can be passed as a comma separated list into the function, but of course this list can get horribly long.

Another use of variables is to programmatically build a chunk of code to be executed, whether that be in a load script or in the front end.

What we need to do here is build the list of holiday dates from a spreadsheet that can be maintained outside of QlikView (perhaps public holidays would be a good suggestion for a DataMarket data source?). This needs to come into a temporary table and then be parsed. I will presume knowledge of the PEEK and FOR functions.

LOAD
$(vFmtDate(FromDate)) as [From Date],
$(vFmtDate(ToDate)) as [To Date],
$(vWorkDays(FromDate,ToDate)) as [Number Of Working Days],
...

It can also be employed in the front end of the application like this:

=$(vWorkDays(min(FromDate),max(ToDate)))

Hopefully this has given you plenty of ideas of how to use variables with parameters to improve your QlikView and Qlik Sense apps.

Please note that pushing variables into parameters can sometimes be temperamental. Ensure you use quotes appropriately in your variable definitions, test everything and tweak if things don’t behave as expected first time.

If you have any further tips on this subject please leave them in the comments below.

Excellent Post. Another way I create the Work days Variable, similar to the one above is by doing the following:

//The below is just a workbook with all the Public Holidays
[Public Holidays]:
LOAD PublicHolidays,
1 as 1
FROM
[G:\DataFiles\Data Tables\Public Holidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

As Sander pointed out, the CONCAT function you have in your example can also be used in conjunction with a variable with a parameter – optimising the code even further. The flexibility of how variables are implemented in the Qlik Analytics Platform means they can be used in some ways that functions can not.

Thanks for the post – will look for opportunities to use this.
Here’s UK bank holidays from 2012. A pretty looking page, but (probably) overcomplicated to get into a load script compared with the time it would take to build a spreadsheet (although it does seem to be properly structured into tables with tags…).https://www.gov.uk/bank-holidays

Hi. I need some help. I am dealing with data which has the same purchase req number across rows with different TAT values. I need to insert a new column and get the sum of the TAT against each unique purchase req number keeping the line item data as it is. Here is the example

This will however have the max value replicated many times in the data, and is likely to lead to double counting in the front end.

Instead, you should only load the Purchase Req# and TAT in the load script, the Sum and Max can then be calculated in the front end. Create a chart, set it to type of Straight Table, add the Req# as the dimension and then have two expressions sum(TAT) and Max(TAT). Be careful using TAT as a Dimension, as duplicates will be removed.

Hi Steve i’m new to qlikview. I have a query. I have two source file inputs (.qvx). Can I pass the value of source1.qvx to source-2.qvx. For Instance If user select year, month and day in source one can that same year, month and day passed as parameters to source2.qvx?

Hi. If you load both QVX files into a single QVW and the two tables associate, then yes… selections made on one QVX’s fields will affect the selection of rows on the other. The trick is getting the correct association. Aim to have one field with a common name between the two tables in the load script (use the AS statement to cause fields to match or not match). As association will be made on the data in that field.

There is a lot written online about these associations, the description in QlikView 11 For Developers is also very good (there was a deal on at the Publishers, Packt, on the eBooks recently that may still be running).

Than you Steve I tried to do the same but the problem here is the two files having year month and day.For instance an agent will suggest loan to customer one day but the customer buys in different day. Here we are checking the AVG of enquirers for offered customers and customers who bought that product in that month.

There are a couple of ways to deal with this. Usually the simplest is to concatenate the two tables, making sure that any fields that have the same content have exactly the same name. Look up help on the CONCATENATE statement to find out how. As it sounds like a lot of the fields will be common (Agent, Customer, Date, Month) this will work well. You may need to add a new field to each table, to specify row type, eg. ‘Suggested’ as [Event Type], and ‘Purchased’ as [Event Type],.

The other route is to create a Link Table with the common fields in, and link both of the other tables to this. It’s a good approach in some cases, but I suspect not the best in yours.

Again, QlikView 11 For Developers has some good sections on these approaches.

The variable is being set in the front end, and setting your expression has an as statement I presume this is in the load script. This means you would have to pull through all of the data again to see the change. Applying the expression in the front end would appear to be the better bet.

QlikView certainly can handle expressions of that kind. If you are having issues then the first place to look is probably your data modelling and looking of tables.

This isn’t entirely straight forward. I would suggest building the stock as at week end in a loop on the load script, where you loop for each week from the start of your data. Each time through you need to include all rows up to and including this week to get the as at position. This is something you will almost certainly want to do as an incremental load, rather than going from the start all of the time.

Nice post Steve, thanks.
I tried to nest variables that used parameterized variables without success. Is there something else I need to do to the $1 parameter to allow it to pass to the next layer?
When I hardcoded the parameter it worked fine so I think the chain is the issue.

Hi David, I can’t see why a variable containing $(vSecondVariable($1)) wouldn’t work. Variables with parameters can be a bit temperamental about how they are called and used. How many possible values are there in the parameter, would if(‘$1’ = ‘A’, $(vSecondVariable(A)), if(‘$1’ = ‘B’, $(vSecondVariable(B)), etc. be acceptable?

I don’t believe it will have an impact on performance, as the engine will see the expressions after the variable replacement has taken place and cache the end expression – exactly the same as if you had a separate variable for each permutation that you are achieving with a parameter.

What it will do is make your code much tidier and development more efficient, which should in turn make your application better.

Hi. I don’t know if you can help me, but I’ll give it a try.
I am working on a QV model, extracting data from an oracle DB.
I am using two SELECT sentences to call two different sets of results from the same table.
I then want to create a combined variable in each SELECT, in order to correlate the two tables. I need the combined variable to get unique values and be able to correlate the two tables. I don’t seem to be able to create this new variable.
I hope I explained myself… could you please help me here?

Hi Carlos. I would probably use RowNo() to add the identifier ( RowNo() as ID, ) and then store the whole table to QVD, with the superset of fields that you need for both tables in your model. You can then load from the QVD twice, and know that the ID will link the two tables. You will need to rename all fields in the second table that are common, except for the ID field.
Does that answer your question?

I am contemplating moving some/all our expressions that are reduced to a variable but what I have found is that there are many permutations depending on what fields we want in the Set analysis. Would it be possible to create the variable with say 4 parameters ($1,$2,$3,$4) and just not always specify all of them when we call the variable?

Hi Chase,
I believe that any parameters not specified are treated as empty strings, you will have to test this though. To know whether variable function without all parameters you will need to think if the syntax still makes sense. You could use an IF statements for this, but it could get messy and not perform so well;

if ‘$1’ = ” then
sum(Dollars)
else
sum({< $1>}Dollars)
end if

Personally I would probably opt for more variables, with different numbers of parameters for each.