Using Sub Routines in Qlik

Using subroutines in Qlik is easier than you would think.

Subroutines can be setup to call various parts of script in different orders than it would normally run. In all my training sessions i explain that Qlik runs from top to bottom in Sense and left to right in QlikView. There are only a few things that would change this:

 

  1. Preceding Loads
  2. Subroutines

Preceding loads can be used to refer to fields you have just created. You utilise the Qlik engine to refer back to a data already in memory. For example:

 

Load *,

If (TotalValue > 100, 'Large', 'Small') as PurchaseSize;

Load

Value,

Quantity,

Value * Quantity as TotalValue

From File.qvd (QVD);

 

In this example the TotalValue field is created in the load from QVD. The script then goes back up to the top and from the table loaded it uses the field created to do something else. Notice the load statement is missing and just has a semi colon at the end, inferring to load from the table below. Preceding loads can be used multiple times in the same table if needs be.

 

Subroutines work in a different way. You can use a control sheet to decide which subroutines to "Call" and specify which order.

 

To set up subroutines, simply add:

Sub Name at the start of the section of script and then End Sub when finished, like the example below:

In the control sheet you can then call these subroutines in any order you wish.

 

You can create subroutines for all sorts of reusable things like creating QVDs.

Leave a Reply

Your email address will not be published. Required fields are marked *