Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Subroutines in load script

tomasz_tru
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)

distinct_temp:

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');

    main_temp:

    NOCONCATENATE LOAD *

    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);

    ELSE

      TRACE Wrong output format;

ENDIF

        

    DROP TABLE main_temp;

NEXT

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)
Attachments
Version history
Revision #:
1 of 1
Last update:
‎2017-06-21 04:34 PM
Updated by: