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.

Qlik Parameter With Variable

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:

let vSrcDateFmt		= 'YYYY-MM-DD';
let vDateFormat		= 'DD MMM YYYY';
set vFmtDate		= Date(Date#($1, '$(vSrcDateFmt)'), '$(vDateFormat)');

And then can be applied to a number of fields like this:

LOAD
	$(vFmtDate(StartDate))	as [Start Date],
	$(vFmtDate(EndDate))		as [End Date],
	$(vFmtDate(Updated))		as [Updated Date],
...

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.

The code for building our variable looks like this:

Temp_PublicHols:
LOAD
	[Public Holiday Date]
FROM [$(vSourceData)\MyApp-Lookups.xlsx]
(ooxml, embedded labels, table is [Public Holidays]);

let vPubHols = '';

for iHol = 0 to NoOfRows('Temp_PublicHols') - 1;
	let vPubHols = vPubHols &
			if(vPubHols = '', '', ',') & chr(39) &
			Date(peek('Public Holiday Date', iHol, 'Temp_PublicHols')) & chr(39);
next

set vWorkDays		= (RangeMax(NetworkDays($1, $2, $(vPubHols))-1,0));

DROP TABLE Temp_PublicHols;

When run over a spreadsheet of UK bank holidays the resulting variable contains this code:

(RangeMax(NetworkDays($1, $2, '28/12/2015','25/12/2015','31/08/2015','25/05/2015',
'04/05/2015','06/04/2015','03/04/2015','01/01/2015','02/05/2016','28/03/2016',
'25/03/2016','01/01/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016',
'02/01/2017','14/04/2017','17/04/2017','01/05/2017','29/05/2017','28/08/2017',
'25/12/2017','26/12/2017')-1,0))

The load script can then use this variable like this:

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.