Qlik Script Failing Silently? How to Catch It

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

A Qlik reload fails on Friday night. Nobody notices until Monday. The CEO opens the dashboard and sees last week’s numbers presented as this week’s. That’s what silent failures cost.

Why Does Error Handling Belong in Production Scripts?

Error handling isn’t a development best practice. It’s the difference between your finance team making decisions on current data vs stale data.

Every silent reload failure is a day where the dashboard lies. A data quality framework can detect anomalies after load – but error handling prevents the wrong data from loading in the first place. These two layers work together. See the data quality gates guide for the validation layer that runs after a successful load.

What Can You Learn About Error Handling in Load Scripts?

After this guide you will be able to:

1. Use ErrorMode and Error Variables strategically to catch errors in a controlled way.
2. Implement comprehensive Error Logging Frameworks to track errors historically.
3. Apply robust recovery strategies (fallback mechanisms) for critical load operations.
4. Implement proactive validation checks to prevent errors before they occur.

Time investment: 30 min reading + 4 hrs hands-on. Quick win: In 15 minutes you will have your first error checks in place.

What Are the 4 Pillars of Error Handling?

What Is ErrorMode and Why Does It Matter?

The SET ErrorMode statement is the most important tool for controlling script behavior predictably. For the full reference, see the ErrorMode system variable reference.

How Does ErrorMode = 1 (Default: Stop on Error) Work?

// Default: script stops on error
LOAD * FROM Sales.xlsx (ooxml, embedded labels);
// On error: script aborts, no further processing

Use this for critical loads where incorrect data is unacceptable, or during development. The default behavior is safe but gives you no recovery path.

How Does ErrorMode = 0 (Continue on Error) Work?

SET ErrorMode = 0;  // Do not abort on error

LOAD * FROM Sales.xlsx (ooxml, embedded labels);
// On error: script continues, error variables are set

IF ScriptError <> 0 THEN
    TRACE Sales.xlsx failed: $(ScriptErrorDetails);
    // Insert recovery logic here
ENDIF

SET ErrorMode = 1;  // IMPORTANT: Reset to default!

Use this for non-critical or optional data sources, and for production loads where a fallback is implemented. Check for errors immediately – do not wait.

How Do I Capture Error Information with Error Variables?

After every operation, Qlik provides system variables with detailed error information. The ScriptErrorCount variable reference documents all available error variables.

Save error variables immediately. Error variables are reset to 0 after EVERY successful script statement. If the next statement succeeds, it overwrites the error status of the previous one.

SET ErrorMode = 0;

// Operation with error
LOAD * FROM Sales.xlsx (ooxml, embedded labels);
LET vError1 = ScriptError;  // Saved IMMEDIATELY
LET vErrorDetail1 = ScriptErrorDetails;

// Next statement (successful)
LOAD * FROM Products.xlsx (ooxml, embedded labels);
// ScriptError is now 0, regardless of whether Sales.xlsx failed.

Variables are essential for tracking error state across your script. Learn how to structure variables and include files for maintainable error handling in the variables and include files guide.

How Does the Basic Error Handling Pattern and Recovery Work?

This is the standard pattern to use for every critical load operation.

SET ErrorMode = 0;  // Continue on error

// Critical operation
LOAD
    OrderID,
    CustomerID,
    Amount
FROM Sales.xlsx
(ooxml, embedded labels);

// Check for error
IF ScriptError <> 0 THEN
    TRACE Failed to load Sales.xlsx;
    TRACE Details: $(ScriptErrorDetails);
    TRACE Code: $(ScriptError);

    // Recovery: fall back to backup QVD
    LOAD * FROM Sales_LastGood.qvd (qvd);
    TRACE Loaded backup data;
ELSE
    TRACE Sales.xlsx loaded successfully;
ENDIF

SET ErrorMode = 1;  // Restore default

Recovery works by loading backup data from a QVD when an error code is detected. On a successful load, the IF condition skips the recovery block entirely.

What Is the Error Logging Framework?

A structured logging framework lets you monitor all errors over time. Data is collected in a central table and stored in a QVD that you can analyze in a separate data quality framework dashboard.

//===============================================
// Error Logging Framework
//===============================================

// 1. Initialize log if it does not exist yet
IF NOT FileSize('[Logs\ErrorLog.qvd]') > 0 THEN
    Error_Log:
    LOAD
        Timestamp#(Null()) as ErrorTime,
        Text(Null()) as ScriptSection,
        Num(Null()) as ErrorCode,
        Text(Null()) as ErrorMessage
    AUTOGENERATE 0;
ENDIF

// 2. Load existing log (if present)
Error_Log:
LOAD * FROM [Logs\ErrorLog.qvd] (qvd);

// 3. Define SUB for reusable logging
SUB LogError(vSection, vErrorCode, vErrorMsg, vRecovery)
    CONCATENATE(Error_Log)
    LOAD
        Now() as ErrorTime,
        '$(vSection)' as ScriptSection,
        $(vErrorCode) as ErrorCode,
        '$(vErrorMsg)' as ErrorMessage,
        '$(vRecovery)' as RecoveryAction
    AUTOGENERATE 1;

    TRACE $(vSection) | Code: $(vErrorCode) | $(vErrorMsg);
END SUB

Usage:

SET ErrorMode = 0;

LOAD * FROM Sales.xlsx (ooxml, embedded labels);

IF ScriptError <> 0 THEN
    CALL LogError('Data Load', $(ScriptError), '$(ScriptErrorDetails)', 'Loaded from backup');

    // Recovery
    LOAD * FROM Sales_LastGood.qvd (qvd);
ENDIF

// Save error log at the end
STORE Error_Log INTO [Logs\ErrorLog.qvd] (qvd);

SET ErrorMode = 1;

How Can You Prevent Errors by Pre-Checking Files?

The most effective error handling is prevention. Check whether a file actually exists before attempting to load it. Qlik’s FileSize() function makes this straightforward.

// SUB: Checks whether a file exists and is larger than 0 bytes
SUB CheckFileExists(vFilePath, vFileExistsVariable)
    LET vFileSize = FileSize('$(vFilePath)');

    IF IsNum(vFileSize) AND $(vFileSize) > 0 THEN
        LET $(vFileExistsVariable) = 1;
        TRACE [CHECK] File exists: $(vFilePath);
    ELSE
        LET $(vFileExistsVariable) = 0;
        TRACE [CHECK] File NOT found: $(vFilePath);
    ENDIF
END SUB

// Usage: Check whether Sales.xlsx exists
CALL CheckFileExists('Data\Sales.xlsx', vSalesFileExists);

IF $(vSalesFileExists) = 1 THEN
    LOAD * FROM Data\Sales.xlsx (ooxml, embedded labels);
ELSE
    TRACE Sales.xlsx not found, trying backup;

    CALL CheckFileExists('Backup\Sales_LastGood.qvd', vBackupExists);

    IF $(vBackupExists) = 1 THEN
        LOAD * FROM Backup\Sales_LastGood.qvd (qvd);
    ELSE
        TRACE No data available! Aborting.;
        EXIT SCRIPT;
    ENDIF
ENDIF

• FileSize(): Returns the file size in bytes (NULL or -1 if the file does not exist).
• IsNum(): Ensures that FileSize returned a valid numeric value.
• Cascade: Check in order: primary source then backup source then abort with EXIT SCRIPT.

How Do You Handle Database Connection Errors?

Database connection errors are especially common. A test query before the actual data load helps catch them early.

SET ErrorMode = 0;

// Test the database connection
LIB CONNECT TO 'DatabaseConnection';

// Test query: select 1 record
Test_Connection:
LOAD 1 as TestField;
SQL SELECT 1 as TestField FROM DUAL;

IF ScriptError <> 0 THEN
    TRACE Database connection failed! Code: $(ScriptError);

    // Recovery: use cached QVD data
    Sales:
    LOAD * FROM Sales_Cache.qvd (qvd);
    TRACE Using cached data from QVD;
    SET vDataFreshness = 'Cached';

ELSE
    // Connection OK, load fresh data
    DROP TABLE Test_Connection;
    //... (Load Fresh Data Logic)...

    // After successful load: update cache
    STORE Sales INTO Sales_Cache.qvd (qvd);
    TRACE Fresh data loaded and cached;
    SET vDataFreshness = 'Fresh';

ENDIF

SET ErrorMode = 1;

On a connection error the script switches to the cache (last working QVD). On a successful load the cache is updated. This pattern pairs well with incremental loads where a failed delta load should fall back to the last full load.

What Is the Rollback Strategy for Atomic Loads?

A rollback ensures that if the new load fails or the data is corrupt, the last known good version of the data is preserved. Atomic Load means: all or nothing.

// Step 1: Backup current QVD
IF FileSize('Sales.qvd') > 0 THEN
    EXECUTE cmd.exe /C copy "Sales.qvd" "Sales_LastGood.qvd";
    TRACE Current QVD backed up;
ENDIF

// Step 2: Load new data (into a temporary table)
SET ErrorMode = 0;
Temp_Sales:
LOAD *
FROM Sales.xlsx (ooxml, embedded labels);

// Step 3: Validate the new data
LET vNewRowCount = NoOfRows('Temp_Sales');
LET vNullCount = Sum(If(IsNull(OrderID), 1, 0), 'Temp_Sales');

TRACE New data: $(vNewRowCount) rows, $(vNullCount) nulls;

// Step 4: Decision - Accept or Rollback
IF ScriptError <> 0 THEN
    TRACE Load failed, rolling back;
ELSEIF $(vNewRowCount) = 0 THEN
    TRACE No data loaded, rolling back;
ELSEIF $(vNullCount) > $(vNewRowCount) * 0.1 THEN
    TRACE Too many NULLs (>10%), rolling back;
ELSE
    // Accept new data
    Sales:
    LOAD * RESIDENT Temp_Sales;
    DROP TABLE Temp_Sales;

    STORE Sales INTO Sales.qvd (qvd);
    TRACE New data accepted and stored;
ENDIF

SET ErrorMode = 1;

A rollback is triggered by technical errors (ScriptError) but also by critical quality issues like too many NULL values. In all failure cases, load from the backup QVD instead.

What Are the Best Practices for Error Handling in Production Scripts?

ErrorMode Management:

• Leave the default (ErrorMode = 1) active – only set to 0 for specific blocks.
• Always reset to 1 once the error-handling block is complete.

Error Variables:

• Save to your own variable IMMEDIATELY after the operation.
• Capture both ScriptError AND ScriptErrorDetails for logging.

Recovery Strategy:

• Implement proactive error prevention with FileSize() checks.
• Use fallback to backup QVDs as the primary recovery strategy.
• Use EXIT SCRIPT only for critical, unrecoverable errors.

How Do I Troubleshoot Common Error Handling Problems?

SET ErrorMode = 0;
LOAD * FROM NonExistentFile.xlsx;

LET vError = ScriptError;  // IMMEDIATELY!
TRACE ScriptError: $(vError);

// Keep only the last 1000 records
Error_Log:
LOAD * FROM [Logs\ErrorLog.qvd] (qvd)
ORDER BY ErrorTime DESC;

FIRST 1000 LOAD * RESIDENT Error_Log;
DROP TABLE Error_Log;

STORE Error_Log INTO [Logs\ErrorLog.qvd] (qvd);

What Are the Next Steps After Implementing Error Handling?

You can now implement robust error handling strategies. Three directions to go from here:

• Data Quality Framework – Prevention: detect anomalies before they reach the dashboard. The layer that runs after a successful load.
• Three-Stage Architecture – Structural solution: the QVD layer, transformation layer, and presentation layer that make error recovery predictable.
• Variables & Include Files – The logging infrastructure: how to structure variables and include files so error handlers are reusable across apps.

Previous: Data Quality Framework | Next: Variables & Include Files