7.2 Data Cleansing, Null Handling & Script Control Flow

Data Cleansing, Null Handling & Script Control Flow

In enterprise analytics deployments, raw data arriving from transactional ERPs, CRM platforms, legacy mainframes, and web APIs is frequently dirty, incomplete, and structurally heterogeneous. Fields contain rogue whitespace, unstandardized case formatting, mixed date representations, and unassigned null values.

A certified Qlik Data Architect must possess deep technical mastery over the QIX engine's internal handling of null values, advanced string cleansing functions, the mechanics of dual date interpretation, and procedural control flow structures (FOR, DO WHILE, IF, SUB/CALL). Orchestrating these script features ensures that data models remain clean, robust, automated, and mathematically accurate.

---

1. The Anatomy of Nulls in Qlik Sense

In relational database systems (RDBMS), NULL represents an unknown or missing value, and relational algebra provides three-valued logic (TRUE, FALSE, UNKNOWN) to handle comparisons. In Qlik Sense, the in-memory associative engine operates differently.

Why Nulls Do Not Associate

In the QIX engine, data is stored in columnar Symbol Tables (token dictionaries) and Data Tables (bit-stuffed index arrays):

• A Null value is not a symbol. It is not stored in the Symbol Table and does not receive an integer token pointer.
• Instead, null values are represented in the data table by an empty bit-pointer flag indicating the complete absence of a value.

CONSEQUENCE OF NULL IN THE ASSOCIATIVE ENGINE:

[Table A: Customers]                   [Table B: Orders]
CustomerID   CustomerName             OrderID   CustomerID   Amount
──────────   ────────────             ───────   ──────────   ──────
101          Alpha Corp               1         101          $500
102          Beta LLC                 2         Null         $1,200 (Unassigned Channel)
Null         Ghost Account            3         102          $750

Associative Behavior:
- Order 2 has CustomerID = Null. It CANNOT associate with Table A's Null row!
- Nulls NEVER link to Nulls across tables.
- Nulls are not listed as selectable values in filter panes; table charts display them as '-'.
- Standard Count(CustomerID) skips nulls entirely.

Making Nulls Interactive: NullAsValue & SET NullValue

When business requirements dictate that analysts must be able to filter, select, and report on missing records (e.g., "Show all orders with missing Sales Reps"), the architect must convert nulls into explicit string symbols during script execution:

// Step 1: Designate fields where nulls should become tangible values
NullAsValue SalesRegion, CustomerSegment, SalesRepCode;

// Step 2: Define the universal string label for converted nulls
SET NullValue = '<Not Specified>';

// Step 3: Load the data table
Customers:
LOAD 
    CustomerID,
    CustomerName,
    SalesRegion,
    CustomerSegment
FROM [lib://Data/Customers.qvd] (qvd);

// Step 4: Deactivate NullAsValue to prevent affecting downstream tables
NullAsNull SalesRegion, CustomerSegment, SalesRepCode;

Operational Result:

• Null occurrences in SalesRegion and CustomerSegment are converted into the literal text symbol '<Not Specified>' in the Symbol Table.
• Business users can select '<Not Specified>' directly in dashboard filter panes, and Count(SalesRegion) evaluates these records properly.
• Always invoke NullAsNull after loading the target tables to restore default engine behavior for subsequent script tabs.

Blanks Are Not Nulls

An empty string ('') or a value made only of spaces is a real value: it gets a symbol, shows up as a selectable blank entry in filter panes, and associates with other blanks. Real nulls come from database NULLs, joins, and forced concatenation; Qlik's help notes that text files cannot contain real NULL values at all. Choose the handling that the filtering requirement needs:

If users must click on "missing" records, convert both blanks and NULLs to one explicit label; if missing values should simply drop out of associations, normalize blanks to NULL. Mixing the two leaves a model where some missing values are selectable and others are not.

Fallback & Replacement Functions: Alt(), Coalesce(), and If(IsNull())

LOAD 
    OrderID,
    
    // Alt(): Evaluates parameters sequentially; returns first valid NUMERIC/DUAL value
    // Ideal for fallback dates, prices, or numeric codes
    Alt(DiscountRate, StandardRate, 0.05) AS EffectiveRate,
    
    // Coalesce(): Returns the first NON-NULL value (supports text strings and duals)
    Coalesce(ShippingAddress, BillingAddress, 'Address Unknown') AS FinalAddress,
    
    // If(IsNull()): Traditional conditional evaluation for row-level replacement
    If(IsNull(DeliveryDate), 'Pending Dispatch', Text(DeliveryDate)) AS DeliveryStatus
FROM [lib://Data/Orders.qvd] (qvd);

---

2. String Cleansing & Data Sanitization Functions

Transactional systems regularly produce inconsistent strings that disrupt associative matches (e.g., ' US ' vs 'US', or '(555) 019-2834' vs '5550192834'). Qlik Sense provides targeted cleansing functions to sanitize text at ingestion.

PurgeChar() vs. KeepChar()

• PurgeChar(Text, StripCharacters): Removes every occurrence of the characters specified in the second argument. It acts as a character-level blacklist.
• KeepChar(Text, PreserveCharacters): Preserves only the characters specified in the second argument, purging everything else. It acts as a character-level whitelist.

LOAD 
    RawPhone,
    RawTaxID,
    RawAccountCode,
    
    // Blacklist approach: Strip parentheses, dashes, dots, and spaces from phone numbers
    PurgeChar(RawPhone, ' ()-./') AS CleanPhone,
    
    // Whitelist approach: Retain ONLY digits for national tax IDs
    KeepChar(RawTaxID, '0123456789') AS CleanTaxID,
    
    // Whitelist approach: Retain alphanumeric characters, stripping rogue symbols
    KeepChar(Upper(RawAccountCode), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') AS CleanAccountCode
FROM [lib://Staging/Vendors.csv] (txt, utf8, embedded labels);

Whitespace & Case Normalization

• Trim(Text): Removes leading and trailing standard ASCII 32 spaces. LTrim() removes only leading spaces; RTrim() removes only trailing spaces.
• Upper(Text) / Lower(Text) / Capitalize(Text): Standardizes casing. Because Qlik's associative engine treats 'Apple' and 'APPLE' as distinct symbols unless coerced, dimensions used for joins or keys must always be standardized using Upper(Trim(KeyField)).

---

3. String-to-Date Parsing: Interpretation vs. Formatting

The most frequent cause of broken calendar logic, incorrect set analysis, and failed sorting in Qlik applications is confusing Interpretation functions with Formatting functions.

The Dual Date Architecture

In Qlik Sense, every temporal value (date, time, timestamp) is internally stored as a Dual value:

• Numeric Representation: A 64-bit floating-point number representing the count of days since December 30, 1899. For example, 46310 represents October 15, 2026. The fractional component represents time (e.g., 0.5 = 12:00 PM noon).
• Text Representation: The human-readable string displayed to users (e.g., '2026-10-15').

Mathematical sorting, chronological ranges, Year(), Month(), and set analysis date inequalities (">=2026-01-01") strictly evaluate the numeric component.

INTERPRETATION (#) vs. FORMATTING NO (#):

[Raw Text String in Source: '15/10/2026']
                  │
                  ▼
┌────────────────────────────────────────────────────────┐
│ Date#(RawString, 'DD/MM/YYYY')                         │
│ (INTERPRETATION FUNCTION: Has '#' suffix)              │
│ Tells engine: Parse day first, month second, year third│
└─────────────────────────┬──────────────────────────────┘
                          │
                          ▼
      [Internal Dual Value: Number = 46310, Text = '15/10/2026']
                          │
                          ▼
┌────────────────────────────────────────────────────────┐
│ Date(NumericValue, 'YYYY-MM-DD')                       │
│ (FORMATTING FUNCTION: No '#' suffix)                   │
│ Tells engine: Format display text as '2026-10-15'      │
└─────────────────────────┬──────────────────────────────┘
                          │
                          ▼
      [Final Dual Value: Number = 46310, Text = '2026-10-15']

The Two-Step Enterprise Date Pattern

If an architect applies Date(RawString, 'YYYY-MM-DD') directly to a string that does not match the application's default DateFormat environment variable, the result has no valid numeric date: depending on the input you get unchanged text or NULL, but never a correctly parsed date, and no error is raised.

To parse raw text strings into certified dual dates, always nest the Interpretation function inside the Formatting function:

LOAD 
    RawDateString,      // e.g., '20261015'
    RawTimestampString,  // e.g., '15-10-2026 14:30:00'
    
    // Correct Date Pipeline: Interpret raw pattern, then format output
    Date(Date#(RawDateString, 'YYYYMMDD'), 'YYYY-MM-DD') AS OrderDate,
    
    // Correct Timestamp Pipeline
    Timestamp(
        Timestamp#(RawTimestampString, 'DD-MM-YYYY hh:mm:ss'), 
        'YYYY-MM-DD hh:mm:ss'
    ) AS OrderTimestamp,
    
    // Numeric extraction
    Num(Num#(RawPriceString, '####.##', '.', ','), '#,##0.00') AS UnitPrice
FROM [lib://DropFolder/Transactions.txt] (txt, utf8, embedded labels);

---

4. Script Control Flow Structures

Qlik Sense provides procedural control flow statements that transform static load scripts into intelligent, automated ETL engines.

1. FOR ... NEXT (Numeric Iteration)

Used for fixed counting loops, historical partitioning, or batch aggregations:

// Ingest 5 historical annual partitions
FOR vYear = 2022 TO 2026
    TRACE >>> Loading historical archive for Year: $(vYear)...;
    
    Fact_History:
    LOAD 
        OrderID,
        CustomerID,
        Amount,
        $(vYear) AS ArchiveYear
    FROM [lib://QVD_Archive/Orders_$(vYear).qvd] (qvd);
NEXT vYear

2. FOR EACH ... IN FileList() / DirList() (Dynamic File Ingestion)

Used to dynamically discover and ingest files from directories without hardcoding filenames:

SET vSourceFolder = 'lib://DropBox_Feeds/';

// Iterate through all CSV files matching the naming pattern
FOR EACH vFile IN FileList('$(vSourceFolder)Daily_Sales_*.csv')
    TRACE >>> Ingesting file: $(vFile)...;
    
    Staging_Sales:
    LOAD 
        TransactionID,
        StoreCode,
        SaleAmount,
        FileBaseName() AS SourceFileName
    FROM [$(vFile)] (txt, utf8, embedded labels);
NEXT vFile

3. DO WHILE ... LOOP & DO ... LOOP UNTIL (Conditional Iteration)

Ideal for consuming paginated REST APIs or polling until a status condition changes:

LET vPage = 1;
LET vHasMorePages = 1;

DO WHILE vHasMorePages = 1
    LIB CONNECT TO 'REST_API_Orders';
    
    Temp_Page:
    SQL SELECT 
        "order_id", 
        "total_records",
        "page_number"
    FROM JSON (wrap on) "root" 
    WITH CONNECTION (QUERY "page" "$(vPage)");
    
    // Check if current page retrieved all records
    IF NoOfRows('Temp_Page') < 1000 THEN
        LET vHasMorePages = 0; // Terminate loop
    ELSE
        LET vPage = vPage + 1; // Advance to next page
    END IF
LOOP

4. IF ... ELSEIF ... ELSE ... END IF (Conditional Branching)

Used to branch script logic based on environment flags, user selections, or data reload modes:

// Conditional execution based on reload environment
IF '$(vReloadMode)' = 'FULL' THEN
    TRACE >>> Initiating Full Extraction Pipeline...;
    CALL ExecuteFullExtract;
ELSEIF '$(vReloadMode)' = 'INCREMENTAL' THEN
    TRACE >>> Initiating High-Watermark Incremental Load...;
    CALL ExecuteIncrementalExtract;
ELSE
    TRACE >>> Unknown reload mode specified. Aborting script execution...;
    EXIT SCRIPT;
END IF

---

5. Subroutines: SUB and CALL

In enterprise architectures, modularity is essential. Writing identical transformation code across multiple script tabs introduces maintenance debt. The SUB ... END SUB and CALL construct enables developers to build reusable procedures.

Subroutine Declaration & Invocation Pattern

// -------------------------------------------------------------------
// SUBROUTINE: IngestAndCleanStoreFeed
// Parameters: vStoreCode, vRegion
// -------------------------------------------------------------------
SUB IngestAndCleanStoreFeed (vStoreCode, vRegion)
    TRACE >>> Starting ingestion for Store $(vStoreCode) in Region $(vRegion)...;
    
    // NoConcatenate: this field list matches Fact_AllStores, which would otherwise
    // absorb the rows automatically and leave no Temp_Store table to read below
    Temp_Store:
    NoConcatenate
    LOAD 
        InvoiceID,
        '$(vStoreCode)'                                          AS StoreCode,
        '$(vRegion)'                                             AS Region,
        Date(Date#(InvoiceDate, 'YYYYMMDD'), 'YYYY-MM-DD')       AS InvoiceDate,
        KeepChar(TaxCode, '0123456789')                          AS CleanTaxCode,
        Alt(DiscountAmt, 0)                                      AS DiscountAmount,
        GrossAmt - Alt(DiscountAmt, 0)                           AS NetAmount
    FROM [lib://StoreData/Store_$(vStoreCode).csv] (txt, utf8, embedded labels);
    
    // Concatenate into unified production table
    CONCATENATE (Fact_AllStores)
    LOAD * RESIDENT Temp_Store;
    
    DROP TABLE Temp_Store;
    
    TRACE >>> Ingestion completed for Store $(vStoreCode).;
END SUB

// -------------------------------------------------------------------
// SCRIPT EXECUTION CALLS
// -------------------------------------------------------------------
Fact_AllStores:
LOAD * INLINE [InvoiceID, StoreCode, Region, InvoiceDate, CleanTaxCode, DiscountAmount, NetAmount];

CALL IngestAndCleanStoreFeed('ST-101', 'North');
CALL IngestAndCleanStoreFeed('ST-102', 'South');
CALL IngestAndCleanStoreFeed('ST-103', 'West');

Subroutine Architectural Best Practices

• Parameter Isolation: Variables passed into subroutines are evaluated before entry. To prevent variable pollution, clear local temporary variables at the end of the subroutine (SET vTemp = ;).
• Error Containment: Subroutines allow localized error checking and diagnostic TRACE output, isolating failures to specific parameters.

---

6. Variables and Dollar-Sign Expansion Mechanics

Variables in Qlik Sense serve as dynamic holders for strings, numbers, or expressions. How a variable is evaluated depends on the syntax used during its expansion.

Literal Text Substitution: $(vVar)

When a variable is referenced as $(vVar), the QIX script pre-processor performs an immediate, raw string replacement prior to parsing the script statement.

SET vTable = 'Customers';
SET vField = 'CustomerID';

// Evaluates to: LOAD CustomerID FROM [lib://Data/Customers.qvd] (qvd);
LOAD $(vField) FROM [lib://Data/$(vTable).qvd] (qvd);

Runtime Formula Evaluation: $(=expression)

When a variable or expression is preceded by an equals sign inside the dollar-sign operator $(=...), the engine evaluates the expression dynamically and substitutes the calculated scalar result:

// Dynamic calculation of yesterday's date in string format
LET vYesterday = Date(Today() - 1, 'YYYY-MM-DD');

// $(=expression): the expression is evaluated and its result is expanded into the statement
Recent_Orders:
LOAD * FROM [lib://Data/Orders.qvd] (qvd)
WHERE OrderDate >= $(=Floor(Today()) - 30);   // expands to a number such as 46280

The Quotes Trap in Dollar-Sign Expansion

A classic source of syntax errors occurs when expanding text strings inside SQL or Qlik WHERE clauses:

SET vCountry = 'United Kingdom';

// FLAWED: Expands to: WHERE Country = United Kingdom;
// The compiler interprets United and Kingdom as unquoted keywords -> SYNTAX ERROR!
LOAD * FROM [lib://Data/Orders.qvd] (qvd) WHERE Country = $(vCountry);

// CORRECT: Wrap expansion in single quotes
// Expands to: WHERE Country = 'United Kingdom';
LOAD * FROM [lib://Data/Orders.qvd] (qvd) WHERE Country = '$(vCountry)';

---

7. Cleansing & Control Functions Reference Matrix

---

8. Enterprise Production Scenario: Automated Multi-Vendor Feed Cleansing

Business Context

A global logistics provider ingests daily shipment manifests from 25 third-party freight carriers. Each carrier uploads a CSV file into a shared cloud storage folder. The incoming files exhibit four critical data quality defects:

1. Carrier A formats dates as 'DD/MM/YYYY', Carrier B formats dates as 'YYYY-MM-DD', and Carrier C formats dates as 'YYYYMMDD'.
2. Driver telephone numbers contain random punctuation, extensions, and country codes.
3. Consignee postal codes contain illegal special characters that break geospatial mapping.
4. Approximately 8% of shipments have no assigned delivery driver (DriverID = Null), preventing operational dispatchers from selecting unassigned loads in Qlik dashboards.

Script Implementation

The architect engineered a dynamic ingestion and sanitization pipeline:

// CSV files cannot contain real NULLs: read empty cells as NULL first
SET NullInterpret = '';

// Then turn those NULLs into a selectable label for dispatch fields
NullAsValue DriverName, DeliveryRoute;
SET NullValue = '<UNASSIGNED DISPATCH>';

Fact_Shipments:
LOAD * INLINE [TrackingNumber, CarrierCode, ShipDate, CleanPhone, CleanPostalCode, DriverName, DeliveryRoute];

// Dynamic loop across all carrier CSV manifests
FOR EACH vManifestFile IN FileList('lib://CloudDrop/*.csv')
    
    // Extract the carrier code from the file name, e.g. 'Daily_CARRIERA_20261015.csv'.
    // FileBaseName() only works inside a LOAD, so parse the loop variable instead.
    LET vCarrier = SubField(SubField('$(vManifestFile)', '/', -1), '_', 2);
    
    TRACE >>> Ingesting and sanitizing manifest for Carrier $(vCarrier)...;
    
    Temp_Manifest:
    LOAD 
        TrackingNumber,
        '$(vCarrier)'                                          AS CarrierCode,
        RawShipDate,
        RawDriverPhone,
        RawPostalCode,
        DriverName,
        DeliveryRoute
    FROM [$(vManifestFile)] (txt, utf8, embedded labels);
    
    // Standardize dates based on carrier profile
    CONCATENATE (Fact_Shipments)
    LOAD 
        TrackingNumber,
        CarrierCode,
        // Carrier-specific Date Interpretation
        If(CarrierCode = 'CARRIERA', Date(Date#(RawShipDate, 'DD/MM/YYYY'), 'YYYY-MM-DD'),
        If(CarrierCode = 'CARRIERB', Date(Date#(RawShipDate, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
        If(CarrierCode = 'CARRIERC', Date(Date#(RawShipDate, 'YYYYMMDD'), 'YYYY-MM-DD'),
        Null())))                                              AS ShipDate,   // unknown profile stays null for QA
        
        // Telephone Whitelist: Retain only digits
        KeepChar(RawDriverPhone, '0123456789')                  AS CleanPhone,
        
        // Postal Code Cleansing: Purge spaces and punctuation
        Upper(PurgeChar(RawPostalCode, ' -.,/()'))              AS CleanPostalCode,
        
        DriverName,
        DeliveryRoute
    RESIDENT Temp_Manifest;
    
    DROP TABLE Temp_Manifest;
    
NEXT vManifestFile

// Deactivate NullAsValue and NullInterpret to restore standard engine behavior
NullAsNull DriverName, DeliveryRoute;
SET NullInterpret = ;

Production Outcomes

• All incoming dates were converted into uniform dual dates (Number = 46310, Text = '2026-10-15'), enabling error-free timeline set analysis.
• Telephone and postal codes were completely sanitized for automated SMS dispatch and geospatial mapping.
• Empty driver cells were read as NULL (via NullInterpret) and then shown in filter panes as '<UNASSIGNED DISPATCH>', allowing managers to immediately identify and assign orphaned shipments.
• Files from an unknown carrier profile kept a null ShipDate instead of an invented date, so the validation step could flag them.

---

9. Common Exam Pitfalls & Traps