Inter-record Functions

Inter-record functions are used to tell Qlik to use or look at values from rows other than the one currently being read. Some functions can be used to look at values in other previously loaded tables.

This article will cover the basic principles of working with inter-record functions in the data load editor and visualisations, including:

• The general function syntax
• Working with inter-record functions in the data load editor
• Working with inter-record functions in visualisations

For a complete, up-to-date list of inter-record functions, please see the Qlik Sense help pages here.

The inter-record function syntax

The syntax will differ depending on the function. For up-to-date syntax, please do refer to the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

It isn’t always easy to decipher the syntax provided in the help pages linked to above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages.

To explain the syntax, we will use a couple of example functions.

Example 1: Previous

The Previous() function returns the value in the previous row that has been loaded. The function can be nested to access rows further back than just the previous row.

Previous(expr)

The rules for reading this are as follows:

1. The function name itself is first and all the parameters you need to enter go between brackets: ().
2. The various elements you will need to enter are separated by commas. In this function, there is only one element: expr.
3. Anything between square brackets [] is an optional parameter. In this function, there are no optional parameters.

An example of this function being used could be:

Previous("Date")

The above function will return the value in the “Date” field that is in the previous row to the one currently being read.

Example 2: Peek

The Peek() function returns the value in an already loaded row in a field, given the row number. The field can be either in the current or a previously loaded table. Note that if the function is peeking values from the current table, the clauses ‘order by’ and ‘where’ will impact the results.

Peek( field_name [, row_no[, table_name ] ])

The rules for reading this are as follows:

1. The function name itself is first and all the parameters you need to enter go between brackets: ().
2. The various elements you will need to enter are separated by commas. In this function, there are three elements: field_name, [row_no] and [table_name].
3. Anything between square brackets [] is an optional parameter. In this function, there optional parameters are the [row_no] (the row number in the table to peek at) and the [rable_name] (the table to peek in). Optional parameters can be omitted if needed. If you do include them, DO NOT write the square brackets. Do not be confused by the double set of square brackets either, the syntax is like this as both functions are optional and are written next to each other.

An example of this function being used with the optional parameters could be:

Peek('Date',0,'MAX_DATE') as LastDate

Imagine that a table called MAX_DATE has been loaded into the app and it contains one row with the latest date an event has occurred. This function will peek the value in the field “Date” in that table in the first (and only in this example) row. You could set a variable equal to this value and use it in other parts of the script.

An example of this function being used without the optional parameter could be:

Peek("Customer ID") as "Previous Customer ID"

This function will peek the value in the previous row of the field “Customer ID”.

How to use inter-record functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use these functions in the load. The rules are:

• All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
• They are particularly useful for coding into variables or for efficiently performing complex calculations on large datasets that would be too load-heavy if they were to be done using joins
• The functions can be nested and combined with other types of functions

A simple example of using the Date#() and Money# functions to read the data correctly is:

ORDER_FREQUENCY:
LOAD
	"Customer ID",
    "Order Date",
    If(Peek("Customer ID") = "Customer ID", Peek("Order Date"), Null()) as "Customer Last Purchase",
    "Order Date" - If(Peek("Customer ID") = "Customer ID", Peek("Order Date"), Null()) as "Days Between Purchases"
    
RESIDENT ORDERS

ORDER BY
	"Customer ID",
    "Order Date";

The resulting table will have, per customer, how many days there are between their orders. The field “Days Between Purchases” can then be averaged per “Customer ID” to get the average time it takes each customer to return and place a new order.

How to use inter-record functions in visualisations

At the time of writing, only three inter-record functions can be used in the load script, as well as visualisations. Others can only be used in the script or can only be used in visualisations. These are FieldIndex(), FieldValue() and FieldValueCount(). To use these in a visualisation:

• The functions can be nested and combined with other types of functions.
• These functions return values from the loaded data table, given the inputs provided, e.g. return the 5th value in field X. This means that the order of the record returned is determined by how the loaded table is sorted and cannot be set within the function.

Here is an example of the FieldValue() function being used to return the first and second values in a field ‘Value’.

The functions that cannot be used in the data load editor are ones used to return values calculated in a different part of the visualisation, e.g. from the row above. For a full list of these functions, check out the Qlik help pages here. An example of one is the Before() function which, when used in a pivot table, will return the value from the previous column. The function syntax is as follows:

before([TOTAL] expr [, offset [, count]])

Here is an example of using the function to calculate year-on-year changes per month. In the result below, the 2022 column is blank as we have no 2021 data, but the 2023 value is the difference per month between the 2023 and 2022 values. See the function used in the column name.