Qlik Sense Data Transformation: String, Math & Date Functions Complete Guide

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

What Can You Learn About Data Transformation in 18 Minutes?

Raw data is rarely analysis-ready. This guide teaches you every Qlik developer’s essential toolkit: transformation functions. You’ll learn how to turn messy data into clean, structured, and meaningful information.

1. String functions for data cleansing and standardization (Trim, Upper, SubField).
2. Date functions for robust time-series analysis and correct interpretation of any date format (Date#, MonthStart).
3. Inter-Record functions like Peek() and Previous() for complex row-to-row calculations (e.g., running totals).

Time investment: 18 min reading + 2.5 hrs hands-on
Prerequisite: Load Strategies understood
Quick Win: Implement a complete string cleansing routine in 10 minutes

How Do I Quickly Clean Customer Names?

The problem: Your customer names are a mess: ” max MUSTERMANN “, “Anna Schmidt”, “PETRA müller”. For clean analysis you need a consistent format.

The solution: Function chaining
In Qlik you can nest functions inside one another. Evaluation always runs from the innermost to the outermost function — like an assembly line:

1. Replace(CustomerName, ' ', ' '): First replaces double spaces with single spaces.
2. Trim(...): The result is passed to Trim, which removes leading and trailing whitespace.
3. Proper(...): The result of Trim is passed to Proper, which capitalizes the first letter of each word and lowercases the rest.

Here you can see this logic applied, combined with a validation to catch empty names.

CleanCustomers:
LOAD
    CustomerID,
    If(Len(Trim(CustomerName)) > 0,
       Proper(Trim(Replace(CustomerName, '  ', ' '))),
       'Name Missing') as CleanCustomerName,
    Lower(Trim(Email)) as CleanEmail
FROM [lib://DataFiles/customers_dirty.csv]
(txt, utf8, embedded labels, delimiter is ';');

Explanation:

• Trim(): Removes whitespace from the beginning and end of a string.
• Proper(): Formats a string in Title Case.
• Replace(): Finds and replaces a substring within a string.
• Len() & If(): We first check with Len(Trim(CustomerName)) whether any text remains after cleansing. Only then do we apply the formatting — otherwise we output a default value.

Checkpoint: Are all names in the CleanCustomerName column consistently formatted? Perfect!

What Are the Transformation Categories at a Glance?

Qlik provides a rich toolbox of functions organized into distinct categories.

Performance note: Inter-Record functions are significantly slower because they limit Qlik’s ability to process data in parallel. Use them deliberately and only when no alternative exists.

How Do I Use String Functions to Transform Text?

How Does Text Normalization and Parsing Work with SubField()?

SubField() is one of the most useful string functions. It splits a string into its component parts based on a delimiter.

Analogy: SubField('Max Mustermann', ' ', 2) is like saying: “Take the text ‘Max Mustermann’, split it at every space, and give me the second word.” The result would be ‘Mustermann’. Using -1 as the third parameter returns the last part.

Here we use various string functions to split names and validate email addresses.

CustomerCleaning:
LOAD
    CustomerID,
    CustomerName,
    SubField(CustomerName, ' ', 1) as FirstName,
    SubField(CustomerName, ' ', -1) as LastName,
    If(Index(Email, '@') > 0 AND Index(Email, '.') > Index(Email, '@'),
       Lower(Trim(Email)),
       'Invalid Email') as CleanEmail
FROM [lib://DataFiles/customer_data.csv];

How Do I Use Qlik Date Functions for Time Manipulation?

For string manipulation functions like Trim(), Upper(), and SubField(), see the Qlik string functions reference.

What Is the Difference Between Date#() and Date()?

This is the most important concept for date handling in Qlik. A date like “05.10.2025” is initially just text to Qlik. It must be converted into Qlik’s internal numeric date format before you can perform calculations with it.

• Date#(text, 'format') (The “Interpreter”): This function interprets a string. It reads the text and understands it according to the format template (e.g., ‘DD.MM.YYYY’) to convert it into a number.
• Date(number, 'format') (The “Formatter”): This function takes a numeric date representation and formats it for display, e.g., as ‘Oct 2025’.

Practical rule: You almost always use both together: Date(Date#(DateAsText, 'Input-Format')).

The Coalesce() command tries to interpret the date with multiple formats until one succeeds. This is very robust when working with mixed data sources.

DateConversion:
LOAD
    RecordID,
    DateString,
    Date(
      Coalesce(
        Date#(DateString, 'DD.MM.YYYY'),
        Date#(DateString, 'YYYY-MM-DD'),
        Date#(DateString, 'MM/DD/YYYY')
      )
    ) as AutoDate
FROM date_samples.csv;

What Functions Does the Business Calendar Offer?

Once you have a correct date field, you can effortlessly create calendar dimensions for your analyses.

BusinessDates:
LOAD
    OrderID,
    OrderDate,
    Year(OrderDate) as OrderYear,
    'Q' & Ceil(Month(OrderDate)/3) as OrderQuarter,
    Month(OrderDate) as OrderMonth,
    WeekDay(OrderDate) as OrderWeekDay,
    MonthStart(OrderDate) as MonthStart,
    AddMonths(YearStart(Today()), -12) as PreviousYearStart
FROM orders.csv;

What Are Inter-Record Functions for Cross-Row Logic?

The full reference for all date and time functions is available in the Qlik date and time functions reference. For advanced data generation scenarios like creating date sequences or expanding records, see how IterNo() for advanced data generation can take your transformations further. These functions enable calculations that access data from previous rows. Important: The table must be correctly sorted using ORDER BY for these functions to return reliable results.

• Previous(field): Accesses the value of field in the immediately preceding row. Ideal for calculating deltas.
• Peek('field', row_number, 'table'): Much more powerful. Accesses the value of field in any already-loaded row.
• RangeSum(Above(...)): A common pattern for calculating running totals.

Here we calculate a running total and the order sequence per customer.

RunningTotals:
LOAD
    OrderID,
    CustomerID,
    OrderDate,
    Amount,
    Amount - Previous(Amount) as AmountDelta,
    If(CustomerID = Previous(CustomerID), Previous(CustomerOrderSequence) + 1, 1) as CustomerOrderSequence
FROM orders.csv
ORDER BY CustomerID, OrderDate;

How Do I Implement Business Rules with Conditional Logic?

Conditional functions are the backbone of business logic in your load script.

• If(condition, then, else): The universal function for if-then logic. Can be nested to any depth.
• Pick(Match(field, 'A', 'B', 'C'), 'Value A', 'Value B', 'Value C'): An elegant and often more readable alternative to nested If statements when comparing a value against a fixed list of options.
• Coalesce(field1, field2, ..., default): Returns the first non-null value from a list of fields. Perfect for defining a hierarchy of fallback values.

CustomerSegmentation:
LOAD
    CustomerID,
    TotalRevenue,
    Region,
    PreferredEmail,
    BusinessEmail,
    If(TotalRevenue > 10000, 'VIP',
        If(TotalRevenue > 5000, 'Premium', 'Standard')) as Segment,
    Pick(Match(Region, 'North', 'South', 'East'),
        'Northern Region', 'Southern Region', 'Eastern Region') as RegionName,
    Coalesce(PreferredEmail, BusinessEmail, 'No Email Found') as ContactEmail
FROM customer_analysis.csv;

How Do I Troubleshoot Data Transformation Issues?

Why Is Date#() Returning NULL?

Problem: Although the date in DateString looks correct, the conversion fails.

Cause: Most often the format specified in the second parameter of Date#() does not exactly match the string. Watch for details like . vs. / vs. -, or YYYY vs. YY.

Solution: Create a debugging table where you test multiple formats to identify the correct one.

DateDebug:
LOAD
    DateString,
    Date#(DateString, 'DD.MM.YYYY') as GermanFormat,
    Date#(DateString, 'MM/DD/YYYY') as USFormat,
    Date#(DateString, 'YYYY-MM-DD') as ISOFormat
FROM date_problems.csv;

What Are the Next Steps in the Data Transformation Course?

Up next: Transposing Data – CROSSTABLE vs GENERIC LOAD

Related topics:

• ApplyMap vs JOIN – Performance Optimization
• Transposing Data – CROSSTABLE vs GENERIC LOAD
• Incremental Loading – Loading Only Changes

Previous: ApplyMap & Lookups | Next: CROSSTABLE & GENERIC LOAD