Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.

Subroutines in load script

Valued Contributor

Subroutines in load script

Subroutine lets you  avoid unnecessary code duplication and simplify script itself. It's basically a procedure that can get parameters and run any scripting possibilities Qlik have. Imagine you have to store data in a separate files depend on date or sales department. You will do that several times in an application. Here's an example subroutine that will help:

What it is doing:

    1. Take parameters: loaded  table to be divided, field for division, name of output folder library, prefix for the output files (ex. 'Sales by month - '), and output format (TXT or QVD),
    2. Load distinct values of field for division,
    3. Loop through fields for division, select appropriate data and store it in a separate file,
    4. Clean up.

Sub StoreDividedTable(vTableToDivide,vDivideField, vOutputLib, vFilePrefix, vOutputFormat)


LOAD DISTINCT $(vDivideField) AS value_distinct RESIDENT $(vTableToDivide);

LET NumRows=NoOfRows('distinct_temp');

FOR i=1 TO NumRows

LET value_loop = peek('value_distinct',$(i)-1, 'distinct_temp');



    RESIDENT $(vTableToDivide)

    WHERE $(vDivideField) = '$(value_loop)';

    IF '$(vOutputFormat)' = 'TXT' THEN

      STORE main_temp  INTO [lib://$(vOutputLib)/$(vFilePrefix)$(value_loop).txt](txt, delimiter is '\t');

    ELSEIF '$(vOutputFormat)' = 'QVD' THEN

      STORE main_temp  INTO [lib://$(vOutputLib)/$(vFilePrefix)$(value_loop).qvd](qvd);


      TRACE Wrong output format;



    DROP TABLE main_temp;


DROP TABLE distinct_temp;

LET i = null();

LET NumRows = null();

LET value_loop = null();

LET value_loop = null();

End Sub;

// And all you have to do now is to call it with parameters:

call StoreDividedTable('Orders', 'Site', 'out', 'Orders by unit - ', 'QVD');

Tags (1)
Version history
Revision #:
1 of 1
Last update:
‎06-21-2017 04:34 PM
Updated by: