Chapter 3. Scripting

In this chapter, we will discuss the creation of optimized and well-structured scripts for a Qlik Sense application. We are going to cover the following topics:

• Structuring the script
• Efficiently debugging the script
• Packaging the code in script files
• How to use subroutines in Qlik Sense^®
• Optimizing the UI calculation speed
• Optimizing the reload time of the application
• Using a For each loop to load data from multiple files
• Using the Concat() function to store multiple field values in a single cell
• Executing command-line programs within the script

Introduction

What is a script in Qlik Sense? In layman's terms, a script instructs the Qlik Sense engine on how to extract the data from the data source and what to do with it.

It forms an essential component of the ETL process. Hence, it is important to have a well-structured script in order to load the data efficiently. A good understanding of how to optimize an ETL process leads to a better data model. A good data model is one of the core components alongside well-written expressions to realize a good user interface performance.

Structuring the script

The techniques for adding structure to the script is something that comes naturally to experienced developers. This is because they have often learned it the hard way, through other people's work, and spent additional time understanding that the script can be made easier with a couple of simple additions. Again, this is something that won't be covered in user guides, but it is a very important skill for new developers to have under their belt.

Getting ready

In this example, we will generate the required data automatically in the script.

How to do it…

1. Create a new Qlik Sense application and name it Structuring Scripts.
2. Create a new section in the Data load editor called Change Log.
3. Add the following code:

/*
This application demonstrates the importance of adding 
   structure to the back end script of your applications
 
Change Log:
 
[10/06/2015] Philip Hand: Initial build  
 
*/

 

4. Create another section called Calendar and add the following script:

/*======================...

Efficiently debugging the script

It is good script practice to debug the script in your data load editor before its full execution. This way, the developer minimizes the risk of script failures and also saves on valuable time. The process of debugging makes it possible to monitor every script statement and examine the variable values while the script is being executed. The following recipe explains how to debug the Qlik Sense script efficiently.

Getting ready

Load the following script, which gives information about the Products and Customers in the Qlik Sense Data load editor. The sample code is available for download from the Packt Publishing website:

Products Temp: 
LOAD * INLINE [  
Product, ProductID, Sales  
Footwear, F21Lon, 120000 
Tyres, T21Man, 150000  
Mountain Bikes, MB32Lon, 195000  
Road Bikes, RB12Bir, 225000  
]; 
 
Customers: 
LOAD * INLINE [  
Customer, ProductID, City  
Hero, F21Lon, London 
Avon, T21Man, Manchester  
Force1, MB32Lon, London  
Ferrari, RB12Bir, Birmingham...

Packaging the code in script files

Script files are complete blocks of code that are stored in external files such as .qvs or .txt, and they can be included in your application with a single reference. They are conceptually similar to the subroutines that are covered in How to use subroutines in Qlik Sense® recipe in this chapter. However, there is a subtle difference in the usage. QVS simply stands for QlikView Script File.

Everything from data sources, expressions, and visualizations can be governed centrally and the script files can be leveraged in a similar way to help build standards in backend data preparation across multiple applications.

Getting ready

Open a new QlikSense application and create a data connection in a folder where you want to store your script files, as shown in the following example:

How to do it…

1. Open a Notepad document.
2. Copy the following subroutine script (a simplified version of the calendar code from the previous recipe) into the Notepad document:

SUB Calendar(vMinDate...

How to use subroutines in Qlik Sense®

At times, it is mandatory to use the same set of code at different places in the script. To achieve this, developers will sometimes use a copy and paste approach. However, this makes it difficult to maintain and read the script. It is always advised to create subroutines and then simply call them as and when needed.

In the following recipe, we use subroutines to create QlikView Data (QVDs) file and store them in a specific folder. We also generate fields using various functions within the subroutines, which also helps in auditing the QVD files.

Getting ready

1. This recipe makes use of certain functions such as QVDTablename, QVDNoOfFields, and QVDNoOfRecords, which don't work in the normal script mode in Qlik Sense. Hence, we need to activate the legacy mode by following the steps given in the recipe entitled How to activate legacy mode in Qlik sense in Chapter 1, Getting Started with the Data.
2. Once the legacy mode is activated, open Qlik Sense Desktop and...

Optimizing the UI calculation speed

The following recipe discusses the creation of flags in the script and the use of these flags in the chart expressions to optimize the calculation speeds.

A flag can be described as a binary status indicator that is used to indicate certain states of data; for example, creating a new field in the table called MonthToDate Flag. This field can be used to flag records with the number 1 if the record was created in the last month, else we mark the record with a 0.

Using this approach, we can now count the number of records in the table that were created in the last month using the expression SUM([Month To Date Flag]).

A flag is often used to code complex decision logic into the load script so that the binary "yes" or "no" decisions can be quickly identified from the calculations.

Getting ready

For this recipe, we will generate sales data in the script, as defined in the following. Load the following script into the Data load editor:

Calendar:
Load 
    DateID, ...

Optimizing the reload time of the application

There are two methods of loading data from QVDs: optimized and non-optimized. The key point here is that the optimized loads can be up to 100 times quicker than the non-optimized loads.

This speed increase is a result of the data passing directly from the disk (QVD) into the memory (RAM) without being unpacked from its compressed QVD format.

As you may have guessed, the reason every load is not optimized is that we often want to change the data coming out of the QVD. This requires it to be uncompressed before going into memory; hence, it is significantly slower. Just about any change to the data will cause the load to be non-optimized; however, there are a few things that we can do.

Getting ready

1. Open a new QlikSense application and go straight to the Data load editor
2. Create a folder library connection to any location where you want to save example data files, and call that connectionQVDs

How to do it…

1. Copy the following code into the Data load editor...

Using a For each loop to load data from multiple files

Often in a Qlik Sense application, we need to load data from a directory that contains an identical set of data files; for example, sales for each country come in different files for each month. In such a case, we use a wildcard load, in order to fetch the data for our application. The following recipe discusses the data modeling issues encountered when using the wildcard load and how we make use of the For each loop structure in the script to overcome this issue.

Getting ready

For this exercise, we will make use of two sample XLSX files, namely, Apr2015.xlsx and May2015.xlsx, that contain mock sales data for six countries. These files can be downloaded from the Packt Publishing website.

How to do it…

1. Once the source files are downloaded, store them in a folder called ForEachLoadData.
2. Create a folder connection as explained in Chapter 1, Getting Started with the Data, that points to the ForEachLoadData folder. Name the connection as QlikSenseCookBookForEachLoadData...

Using the Concat() function to store multiple field values in a single cell

The information in orders and invoices is typically stored at the header or line level in the database. However, when we display the sales value for a particular order on the UI, it is sometimes desired that all the products for an order are displayed in a single cell rather than on a separate line. The Concat() function is helpful in such a case.

Getting ready

For this recipe, we will make use of an inline data load that gives sales information for orders. Load the following order line information in Qlik Sense:

Orders: 
LOAD * INLINE [  
OrderID,Product, ProductID, Sales  
101,Footwear, F21Lon, 120000 
101,Tyres, T21Man, 150000  
101,Mountain Bikes, MB32Lon, 195000  
102,Road Bikes, RB12Bir, 225000  
102,Chains, F21Lon, 140000 
103,lubricant, T21Man, 56869  
103,Mountain Bikes, MB32Lon, 195000  
104,Road Bikes, RB12Bir, 65233 
]; 
LEFT JOIN 
LOAD OrderID, CONCAT(Product,',') as Products 
Resident 
Orders 
GROUP BY...

Executing command-line programs within the script

In Chapter 1, Getting Started with the Data, we learned how to activate the legacy mode and leverage the use of the EXECUTE statement to run a windows command-line statement to create a copy of a file. The following recipe explains the steps to run a command line using load script variables as a parameter.

Getting ready

The files required for this recipe that are downloaded from the Packt Publishing website come in a zipped folder called QlikSenseData. Extract all the files from this zipped folder, and save them on the hard drive at the desired location if you haven't done this for the previous recipes.

 

How to do it…

Once we have the files saved on the hard drive, go to the Qlik Sense app you have created to apply this recipe.

In order to run a windows command line, perform the following steps:

1. Activate the legacy mode as described in Chapter 1, Getting Started with the Data.
2. Open Qlik Sense Data load editor.
3. Write the following commands, assuming...