Build and use Qlik load script functions

Based on the fact you're reading this, it's assumed you've graduated from the sum(Amount) stage and waltz through Set Analysis with your eyes closed. You have earned your Qlik developer stripes and seek the extra-black belt to show off around the water cooler. When joining the daily calls, you're starting to hear more and more 'expert' or 'ninja' as a catch-all descriptor for your Qlik skills. If this isn't you, you're going to have a bad time.

All joking aside, the concepts we talk about in this article are about as simplistic as it gets to drive home the functionality. As a beginner, any deviation from the examples given may cause you to question career paths and how long you can stay sane. However, this seldom used feature can be used to expand the capabilities of regular expressions, standardize calculations across applications, and accelerate Qlik development. When used effectively, the limits of these functions are generally the developers creativity. Let's begin!

Let vs Set

Key concept number one when setting a variable in the load script is choosing which identifier to use.

LET evaluates the expression and keeps the result as the variable's value. LET is often used when 'peek'ing at values in a table or doing something with numbers. 

SET assigns the value (or expression) as it is without computing. Usually developers believe that using SET is not actually evaluating the formula and just assigning the value of the variable as a string. While it's understandable to think this way, it's not entirely correct. SET still evaluates the expression, but if there are no dollar-sign expansions, then it just returns the string without computing it, which is different from explicitly setting the variable as a string.

Dollar-sign expansion and SET vs LET you have likely heard before and used independently of each other, however the dollar-sign expansion using SET as the identifier together is the keystone of how these load script functions work. We only use SET for these load script functions. Let's take a look at our first example.

SET doeasymath = $1 * $2;

Before we move to the fun stuff you can do with this, let's first see how it works. The $1 and $2 are the functions parameters that get evaluated first before the variable is assigned. Each number reflects the position of the parameter that's passed to it when the function is called. $1 is the first parameter and $2 corresponds to the second. In Qlik Sense, we have created a table and our first measure is

$(doeasymath(rowno(),3))

Parameter $1 = the row number

Parameter $2 = 3

For each row in the table, the row is first multiplied by 3 and then the variable is returned back to the front end. The end result is;

You can call this function multiple times in your dashboard and the result is only associated to the parameters you input. That means that the results are not global and defined on a per chart object basis. You could not (easily) reference the result of one object using these functions to be used in another object using load script functions. 

You're not restricted to only using this in the front end, you can call the function in the load script as well.

DemoRows:
Load
    RecNo() as [Row],
    $(doeasymath(RecNo(),10)) as [Big Numbers]
AutoGenerate(5);

A typical use case for utilizing the function in a table load is when formatting dates or long winded 'if' statements. Any heavily repeated formula is a good candidate for using this method.

Chaining Load Script Functions

What we have spoken to previously is pretty straight forward. Where these functions get interesting is when you start chaining them together. What that means is that the result of one load script function is used in another load script function.

SET doeasymath = $1 * $2;          
SET okhardmath = $1 * $(doeasymath); 

$(okhardmath(4,RowNo()))

The first parameter (4) and the second parameter (RowNo()) are passed to doeasymath first and the result of that is then used in okhardmath. okhardmath uses the first parameter (4) and multiplies it against the result of doeasymath.

So this is cool but when would someone actually use this? While the example above may not be valuable in the real world, complex formulas should be broken out into separate smaller functions (as much as possible without being a burden). When complex formulas are broken out and compartmentalized, it makes life easier when debugging or making edits. Smaller functions may also have use in other formulas which reduces having to maintain and rewrite code.

How many times do you have to repeat yourself

A central repository where all variables, calculations, and code are stored and distributed amongst applications is a bit of a fantasty, but definitely the goal. Many times this strategy is an afterthought and it becomes an act of congress to dissect all current applications and pull out variables and calculations that could be used across Qlik. During this process, it's often found that code is repeated and duplicate procedures and processes are happening across the organization. This effort usually spawns a much larger 'call to efficiency' and restructuring of loading and using data. If this type of effort is outside of the scope and timeline, mini-efforts can be made in the interim.

The first step is to create a folder in a shared folder repository where all Qlik applications can pull from. Create a 'formulas.qvs' file where all of your variables, functions, and reproduced code will reside. The formulas.qvs file is then included in the load scripts of Qlik applications (as required). This file is where the standardization magic will reside. Enforcing developers to use this file and become familiar with the how to utilize it and variables contained within is pivotal.

In the Qlik Sense load script, a folder connection must be established to the .qvs path and your import will look like so

$(Must_Include=LIB://IncludePath/formulas.qvs);

After importing, your 'okhardmath' variables will be available to use. Due to the folder connection requirement in Qlik Sense, the syntax for importing the .qvs in QlikView will be slightly different - just a note to keep in mind.

Taking this a step further, the .qvs file can then be used in version control solutions like Git so rolling back to a previous state is incredibly simple.

Get your feet wet

We've put together a POC using the example scripts above. This application and it's contents can be found on our github page at https://github.com/techbui1ders/Qlik-LoadScriptFunctions . All code is free to use and modify, we would love to hear any feedback or questions you guys may have. We've used load script functions in a few of our other blog posts and applications ( https://github.com/techbui1ders/QlikSense-LicenseAnalyst as an example) and will continue to push the limits of it's usefulness and flexibility. Another example of load script functions coming soon is our ColorPal script (check our github page) which makes branding and coloring your applications amazingly easy.

When it comes to load script functions, understanding the fundamentals, practicing, and testing is the only way to earn that extra-black belt. We hope this short foray has sparked the creative juices and optimizes your development workflow.