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.

Qlik Parameter With VariableThese 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.

By |2016-06-25T01:57:42+00:00June 16th, 2016|Load Script, Qlik Sense Tutorials, QlikView Tutorial|35 Comments

About the Author:

Steve is owner and principal consultant at Quick Intelligence. He is a Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books.

35 Comments

  1. tahir abbas June 16, 2016 at 7:37 am - Reply

    thanks sir

  2. Sander June 16, 2016 at 1:55 pm - Reply

    Hi Steve, why do you use the for loop? Use a CONCAT in your initial load and then a PEEK to get the string into your variable vPubHols.

    • Steve Dark June 16, 2016 at 4:33 pm - Reply

      Good point, and it would certainly work. I just like the fact that you can see the process that is being carried out with the loop, and you have a bit more control over how things go together.

  3. Michael S. Armentrout June 16, 2016 at 4:08 pm - Reply

    Nice examples! Thank you for sharing!

    • Steve Dark June 16, 2016 at 4:33 pm - Reply

      No problems. More to follow, got a nice Sense specific post lined up next…

  4. Vlad Gutkovsky June 16, 2016 at 5:03 pm - Reply

    I love parameterized variables but for some reason it never occurred to me to use them in load scripts. Mind blown. Thank you, sir.

    • Steve Dark June 16, 2016 at 6:43 pm - Reply

      Glad to be of service!

  5. Kevin McCann June 19, 2016 at 9:35 pm - Reply

    excellent post Steve, thanks for sharing this.

    • Steve Dark June 19, 2016 at 10:27 pm - Reply

      Thanks, Kevin, for reading and taking the time to comment.

  6. Stefan June 20, 2016 at 5:09 am - Reply

    Hi Steve

    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);

    [Public Holidays Final]:
    NoConcatenate Load Concat(Num(PublicHolidays),’,’) as [Public Holidays] Resident [Public Holidays] Group by 1;

    Drop Table [Public Holidays];

    Let vPublicHolidays = Peek(‘Public Holidays’);

    Drop Table [Public Holidays Final];

    Leave:
    LOAD *,
    NetworkDays(Date(dFromDate), Date(dToDate),$(vPublicHolidays)) as LeaveDays
    FROM
    G:\Leave.qvd
    (qvd);

    Your way is much less coding so I will definitely try and implement your method into my QlikView Apps.

    Thanks for sharing and keep up the good work!

    • Steve Dark June 20, 2016 at 6:41 am - Reply

      Hi Stefan,

      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.

  7. Jon Sebright June 23, 2016 at 2:08 pm - Reply

    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

    • Steve Dark June 24, 2016 at 2:16 pm - Reply

      Hi Jon, Thanks for the link – as you say not ideally structured. Sometimes copy and paste is the best way.

  8. Durga Anappindi July 11, 2016 at 4:30 am - Reply

    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

    Purchase Req# TAT Sum of TAT Max of TAT
    PR123 100 1500 500
    PR123 200 1500 500
    PR123 300 1500 500
    PR123 400 1500 500
    PR123 500 1500 500
    PR124 10002505 1000
    PR124 250 2505 1000
    PR124 350 2505 1000
    PR124 201 2505 1000
    PR124 302 2505 1000
    PR124 402 2505 1000

    • Steve Dark July 11, 2016 at 9:44 pm - Reply

      Hi Durga,

      If you first load the table exactly as you have it, then you can get the max for each Purchase Request joined onto it with the follows:

      LEFT JOIN (TableName)
      LOAD
      [Purchase Req#],
      max(TAT) as [Max of TAT]
      RESIDENT TableName;

      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.

      Hope that helps.
      Steve

  9. Venky July 31, 2016 at 4:12 pm - Reply

    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?

    • Steve Dark July 31, 2016 at 5:18 pm - Reply

      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).

  10. Venky July 31, 2016 at 5:31 pm - Reply

    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.

    • Steve Dark July 31, 2016 at 5:43 pm - Reply

      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.

  11. sukai August 29, 2016 at 7:05 pm - Reply

    Hi Steve,

    thanks for taking the time to explain so clearly. Excellent

  12. sachin September 23, 2016 at 5:38 am - Reply

    Rarely found in the Community ..excellent post..

  13. Ram February 28, 2017 at 5:22 pm - Reply

    Hi Steve, i have a requiremnt where qlikview is unable to handle this.

    I have a variable say

    Vsales

    I have a front end input box in which im using this variable

    User can enter any value in the input box from 1 to 100

    And the calculation im doing at the backend for this is

    sum (sales)*Vsales/100 as new_slaes

    This new_sales is my base data on top of it i have a lot calculations to be done

    Qlikview is unable to hadle it and i giess qlikview has a disadvante over other tools over here

    I even tried of declaring 1 to 100 numbers in this variable in the script and unable to achieve this

    Does qlikview cnnot handle thos kind of requirements

    I have tired asking many people

    • Steve Dark March 1, 2017 at 7:26 pm - Reply

      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.

  14. rajareddy July 10, 2017 at 2:45 am - Reply

    Thank you so much very use full all

  15. Omar Ben Salem September 7, 2017 at 12:49 pm - Reply

    Hi Steve, I shall first of all thank you for your great post.
    I have a question though, can you please help with this?

  16. Omar Ben Salem September 7, 2017 at 12:50 pm - Reply
    • Steve Dark September 7, 2017 at 12:55 pm - Reply

      I will respond on Qlik Community…

  17. mohamedhedi BAHLOUL May 28, 2018 at 2:22 pm - Reply

    Hello steve,
    i want to calculate a level of stock by week,i can’t find best way to do it in QV
    EXAMPLE
    Partxx,stk at starting, N°of week,production,receipt from supplier
    B0001,50,1,1000,1220
    B0001,Should calculated,2,600,600
    B0001,Should calculated,3,200,300
    B0002,50,1,1000,1220
    B0002,Should calculated,2,600,600
    B0002,Should calculated,3,200,300B0001,50,1,1000,1220
    B0002,Should calculated,2,600,600
    B0002,Should calculated,3,200,300

    B0001
    the level stok end week1=50-1000+1220=270
    the level stok end week2=270-600+650=320
    the level stok end week2=320-200+300=420
    Etc….
    Thank you

    • Steve Dark May 28, 2018 at 9:00 pm - Reply

      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.

  18. David Trent June 12, 2018 at 6:09 pm - Reply

    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.

    • Steve Dark June 13, 2018 at 6:01 am - Reply

      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?

  19. Michael Pierce July 18, 2018 at 10:30 am - Reply

    Hi Steve,

    Does variables with parameters like this improve the calculation performance of Qlik applications?

    • Steve Dark July 19, 2018 at 9:11 am - Reply

      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.

  20. carlos August 10, 2018 at 7:09 pm - Reply

    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?

    • Steve Dark September 13, 2018 at 7:05 am - Reply

      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?

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.