Qlik Sense Subroutines: Reusable Code for Modular Scripts

This is Article 23 of the Qlik Sense Data Modeling Course.

What will you learn about subroutines — reusable code for modular scripts?

After this guide you will be able to:

1. Create and call subroutines using SUB and END SUB (CALL).
2. Pass parameters deliberately and define return values using LET.
3. Build a central subroutine library in an include file.
4. Apply the clean code principle of Single Responsibility to your load processes.

Time investment: 20 min reading + 2 hrs hands-on
Quick win: In 15 minutes you will have your first generic load subroutine implemented

What is the DRY (Don’t Repeat Yourself) principle in subroutines?

How does the SUB…END SUB syntax and the CALL statement work?

// Subroutine definition
SUB SubroutineName(pParameter1, pParameter2)
    // The code is only executed when CALL is invoked
    TRACE Processing: $(pParameter1) and $(pParameter2);
END SUB

// Subroutine call
CALL SubroutineName('Value_A', 'Value_B');

Explanation of keywords:

• SUB: Starts the subroutine definition. The subroutine must be defined in the script before the first call with CALL. See the Qlik SUB/END SUB statement reference for full syntax details.
• Parameters: Defined in parentheses (e.g. pParameter1). It has become standard practice to use the prefix p (for parameter).
• END SUB: Ends the definition.
• CALL: Executes the subroutine and passes the argument values to the parameters. Refer to the Qlik CALL statement reference for usage details.

What is the Basic Subroutine – Generic Loader?

One of the most common use cases is creating a generic loader that only requires the table name and the path as parameters.

SUB LoadTable(pTableName, pFilePath)
    // The statement is generated dynamically from the parameters
   :
    LOAD * FROM
    (txt, codepage is 1252, embedded labels, delimiter is ',');

    // Log result with variable name
    LET vRowCount = NoOfRows('$(pTableName)');
    TRACE Loaded: $(pTableName) - $(vRowCount) rows;
END SUB

// Usage: Load 3 tables with minimal code effort
CALL LoadTable('Customers', 'lib://SourceData/');
CALL LoadTable('Products', 'lib://SourceData/');
CALL LoadTable('Orders', 'lib://SourceData/');

Advantage: If the import format changes (e.g. the delimiter), only the LoadTable subroutine needs to be updated once.

How do you define return values with LET?

Subroutines do not have a direct RETURN command like functions in other languages. Instead, values are passed back to the main script via an output parameter by setting a variable inside the subroutine with LET.

SUB CalculateSum(pField, pTable, vResultVariable)
    // Calculate sum and store the result in the passed variable name
    LET $(vResultVariable) = Sum($(pField), '$(pTable)');

    TRACE Calculated sum of $(pField): $($(vResultVariable));
END SUB

// Usage: The 3rd parameter (vTotalSales) is the name of the variable that receives the output
CALL CalculateSum('Sales', 'SalesData', vTotalSales);
TRACE Total sales: $(vTotalSales);

CALL CalculateSum('Quantity', 'SalesData', vTotalQty);
TRACE Total quantity: $(vTotalQty);

Explanation: We use dollar-sign expansion twice: first, the name of the output variable (e.g. vTotalSales) is passed in. Inside the subroutine, $(vResultVariable) expands to vTotalSales, and LET vTotalSales =… stores the calculated value in the global script memory.

How does the Quality Check Subroutine work?

Subroutines are ideal for automating recurring validation and error-handling tasks. This builds directly on the concepts from the Data Quality Gates article.

SUB CheckQuality(pTable, pField, pThreshold, vIssueCount)
    // Count NULL values in the field
    LET vCount = Count(If(IsNull($(pField)), 1, 0), '$(pTable)');

    // Store result in the output parameter
    LET $(vIssueCount) = $(vCount);

    LET vTotalCount = NoOfRows('$(pTable)');
    LET vNullPercent = Round($(vCount) / $(vTotalCount) * 100, 0.1);

    // Warning if threshold is exceeded
    IF $(vNullPercent) > $(pThreshold) THEN
        TRACE High NULL percentage in $(pTable).$(pField): $(vNullPercent)% > $(pThreshold)%;
    END IF
END SUB

// Usage
CALL CheckQuality('Customers', 'Email', 5, vEmailNulls);
CALL CheckQuality('Orders', 'ShipDate', 1, vShipDateNulls);

TRACE Customer Email Issues: $(vEmailNulls) Records;

Advantage: The logic is centralized and the main script stays clean and focused on the data flow.

How does the Store & Drop Subroutine work?

Complex operations such as storing QVDs and then cleaning up temporary tables can be encapsulated in a single subroutine, including basic error checking.

SUB StoreAndDrop(pTableName, pPath)
    // Check whether the table exists and is not empty
    LET vTableExists = NoOfRows('$(pTableName)') > 0;

    IF $(vTableExists) THEN
        // Store logic (uses path variable from include file)
        LET vStorePath = '$(vQVDPath)$(pTableName).qvd';

        TRACE Storing: $(pTableName) to $(vStorePath);
        STORE INTO (qvd);

        // Clean up
        DROP TABLE;
        TRACE Dropped table: $(pTableName);
    ELSE
        TRACE Table $(pTableName) is empty or does not exist. Store skipped.;
    END IF
END SUB

// Usage
CALL StoreAndDrop('STG_Customers', '');
CALL StoreAndDrop('TRF_Sales', '');

What is the function library pattern and how is it organized?

To get the full benefit of subroutines, move them into a central library (typically 03_Subroutines.qvs) that you include in all your apps via $(Must_Include).

The modular structure of your script layout:

/QlikScripts/

|-- 01_Config.qvs     // Global variables & paths
|-- 02_Functions.qvs  // Utility functions (e.g. date converters)
|-- 03_Subroutines.qvs // All SUB...END SUB blocks
|-- MainApp.qvs      // Calls the libraries (CALL)

The subroutines file itself should group subroutines clearly by function and assign them a clear naming prefix:

What are the best practices for subroutines in modular scripts?

✓ Design & documentation:

• [ ] Single Responsibility: Each subroutine handles exactly one task (e.g. only loading, not loading AND storing).
• [ ] Clear naming convention (prefix table above).
• [ ] Use parameter prefixes (p for input, v for output).

✓ Implementation:

• [ ] Clean up temporary variables at the end of the subroutine (LET vVar = Null()).
• [ ] Error handling: Integrate SET ErrorMode = 0 and IF ScriptError <> 0 into critical load subroutines.

✓ Organisation:

• [ ] Bundle all subroutines in a central include file (03_Subroutines.qvs).
• [ ] Always secure the library call in the main script with $(Must_Include=…).

When your subroutine library grows beyond a single project, consider applying enterprise ETL patterns with the three-stage architecture to structure your reusable code across staging, transform, and model layers.

How do I troubleshoot problems with subroutines in Qlik?

// Correct: Definition before call
SUB MyFunction(pParam)
    TRACE $(pParam);
END SUB
CALL MyFunction('test');

// Wrong: Stores the text "vTotalSales" as a literal string
// LET vResultVariable = Sum(...)

// Correct: Stores the value into the variable whose NAME is held in vResultVariable
LET $(vResultVariable) = Sum($(pField), '$(pTable)');

What are the next steps for subroutines in modular scripts?

With subroutines you are ready to split complex load processes into reusable modules! The next logical step:

1. Three-Stage Architecture: Learn how to use subroutines to separate the different layers (staging, transformation, data model) from each other, dramatically improving maintainability and scalability. Read the next article for that: Three-Stage Architecture.