Record expressions

Introduction

A record expression is an expression that is evaluated for each record. Each new expression adds a new column to the table.

A record expression calculates a value per record and consists of a set of operations and functions that can be applied to the data. This is similar to the use of functions in Excel cells.

Within a record expression, data source attributes can be referenced using their attribute name, which gets the value of that attribute for each record. Expressions can be saved and used in new expressions.

See illustration below for an example of the expression Amount / 100, where the expression is evaluated three times; once for each record.

Creating conditional checks

The following expression contains a conditional check: “If the Amount is higher than 20,000, we want to return the Supplier, otherwise, we want to return the Invoice number”.

Below is an example of a dataset containing three records as the result of the expression:

if(Amount>20000, Supplier, text(Invoice_number)).

The expression is built up in the following manner:

Check if Amount > 20000

• If yes (= then), return Supplier(which is a text value)
• If no (= else), convert Invoice_number to a text value (using the text() function), and return the result of the conversion.
  The result of the expression has the same type (Text) for every record. Converting the Invoice_number to a text value unifies the types of the second (‘then’) and third (‘else’) arguments to the if() function, which must be of the same type.
  
  

  

  
  

NULL values

The value of an attribute can be empty for some records, these get the NULL value. Expressions can make use of NULL values using the expression null. Each function handles NULL values differently. For example, the expression null + 1 will return 1, because the plus operator will ignore NULL values. Also, the average of a set of values ignores the NULL values. However, the expression null mod 2 will return NULL, because the modulus operator treats NULL values as an identity.