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