Qlik Community

Ask a Question

Qlik Sense Documents

Qlik Sense documentation and resources.

Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey

Subroutines in load script


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:
‎2017-06-21 04:34 PM
Updated by: