There are many cases where you need to adjust or transform the field data that is loaded. For example, you may need to concatenate a first name and a last name to a full name, extract part of a product number, convert the data format or multiply two numbers.
You can add calculated fields to manage many cases like this. A calculated field uses an expression to define the result of the field. You can use functions, fields and operators in the expression. You can only refer to fields in the table that you are editing.
You add and edit calculated fields in the table editor of the data manager.
See: Editing a table
Adding a calculated field
Do the following:
-
Click Add field.
The calculated field editor opens.
- Type the name of the calculated field in Name.
-
Define the expression of the calculated field. There are two different ways to do this.
-
Use the 3 (Functions), - (Fields) and ⏩ (Operators) lists to select and insert items into the expression.
The item you select is inserted at the cursor position in Expression.
-
Type the expression for the calculated field in Expression.
You get assistance with functions and fields as you type.
You will see messages displayed below Expression to help you create a correct expression.
-
-
Click Create to create the calculated field and close the calculated field editor.
The Create button is not enabled until you have provided a name, and the expression is validated for correct syntax.
Updating a calculated field
You can change the name or edit the expression of a calculated field.
Do the following:
-
Select Update from the drop-down menu next to the field name.
The calculated field editor opens.
- Edit the name of the calculated field in Name if you want to change it.
-
Edit the expression of the calculated field.
-
Click Update to update the calculated field and close the calculated field editor.
The Update button is not enabled until you have provided a name, and the expression is validated for correct syntax.
Which functions can I use in a calculated field expression?
You can use the functions listed here when you create a calculated field expression. This is a subset of the expressions available in the data load script.
See: Script expressions
The expression cannot result in any aggregation of data from several records, or use inter-record functions to refer to data in other records.
String functions that can be used in a calculated field expression
These functions can be used to modify or extract data in text string format.
| Function | Description |
|---|---|
| Capitalize | Capitalize() returns the string with all words in initial uppercase letters. |
| FindOneOf |
FindOneOf() searches a string to find the position of the occurrence of any character from a set of provided characters. The position of the first occurrence of any character from the search set is returned unless a third argument (with a value greater than 1) is supplied. If no match is found, 0 is returned. |
| Left | Left() returns a string consisting of the first (left-most) characters of the input string, where the number of characters is determined by the second argument. |
| Len | Len() returns the length of the input string. |
| Lower | Lower() converts all the characters in the input string to lower case. |
| LTrim | LTrim() returns the input string trimmed of any leading spaces. |
| Mid | Mid() returns the part of the input string starting at the position of the character defined by the second argument, 'start', and returning the number of characters defined by the third argument, 'count'. If 'count' is omitted, the rest of the input string is returned. The first character in the input string is numbered 1. |
| Replace | Replace() returns a string after replacing all occurrences of a given substring within the input string with another substring. The function is non-recursive and works from left to right. |
| Right |
Right() returns a string consisting of the of the last (right-most) characters of the input string, where the number of characters is determined by the second argument. |
| RTrim | RTrim() returns the input string trimmed of any trailing spaces. |
| SubField |
Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter. Information noteIf you use the Subfield function with two parameters, this can generate more records which may not be reflected in the table editor.
|
| TextBetween |
TextBetween() returns the text in the input string that occurs between the characters specified as delimiters. |
| Trim | Trim() returns the input string trimmed of any leading and trailing spaces. |
| Upper | Upper() converts all the characters in the input string to upper case for all text characters in the expression. Numbers and symbols are ignored. |
Date functions that can be used in a calculated field expression
Qlik Sense date and time functions are used to transform and convert date and time values.
Functions are based on a date-time serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day.
Qlik Sense uses the numerical value of the argument, so a number is valid as a argument also when it is not formatted as a date or a time. If the argument does not correspond to a numerical value, for example, because it is a string, then Qlik Sense attempts to interpret the string according to the date and time environment variables.
If the date format used in the argument does not correspond to the one set in the DateFormat system variable, Qlik Sense will not be able to interpret the date correctly. To resolve this, either change the settings or use an interpretation function.
See: DateFormat
| Function | Description |
|---|---|
| addmonths | This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate. |
| day |
This function returns an integer representing the day when the fraction of the expression is interpreted as a date according to the standard number interpretation. |
| month | This function returns a dual value: a month name as defined in the environment variable MonthNames and an integer between 1-12. The month is calculated from the date interpretation of the expression, according to the standard number interpretation. See: MonthNames |
| monthend | This function returns a value corresponding to a timestamp of the last millisecond of the last day of the month containing date. The default output format will be the DateFormat set in the script. |
| monthname | This function returns
a display value showing the month (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding
to a timestamp of the first millisecond of the first day of the month.
See: MonthNames |
| monthstart | This function returns a value corresponding to a timestamp of the first millisecond of the first day of the month containing date. The default output format will be the DateFormat set in the script. |
| quarterend | This function returns a value corresponding to a timestamp of the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. |
| quartername | This function returns a display value showing the months of the quarter (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the quarter. |
| quarterstart | This function returns a value corresponding to a timestamp of the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. |
| week | This function returns an integer representing the week number according to ISO 8601. The week number is calculated from the date interpretation of the expression, according to the standard number interpretation. |
| weekend | This function returns a value corresponding to a timestamp of the last millisecond of the last day (Sunday) of the calendar week containing date The default output format will be the DateFormat set in the script. |
| weekname | This function returns a value showing the year and week number with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the week containing date. |
| weekstart | This function returns a value corresponding to a timestamp of the first millisecond of the first day (Monday) of the calendar week containing date. The default output format is the DateFormat set in the script. |
| year | This function returns an integer representing the year when the expression is interpreted as a date according to the standard number interpretation. |
| yearend | This function returns a value corresponding to a timestamp of the last millisecond of the last day of the year containing date. The default output format will be the DateFormat set in the script. |
| yearstart | This function returns a timestamp corresponding to the start of the first day of the year containing date. The default output format will be the DateFormat set in the script. |
Formatting and interpretation functions that can be used in a calculated field expression
The formatting functions use the numeric value of the input expression, and convert this to a text value. In contrast, the interpretation functions do the opposite: they take string expressions and evaluate them as numbers, specifying the format of the resulting number. In both cases the output value is dual, with a text value and a numeric value.
For example, consider the differences in output between the Date and the Date# functions.
| Function | Output (text) | Output (numeric) |
|---|---|---|
| Date#('20140831', 'YYYYMMDD') | 20140831 | 41882 |
| Date(41882, 'YYYY.MM.DD') | 2014.08.31 | 41882 |
These functions are useful when your data contains date fields that are not interpreted as dates as the format does not correspond to the date format setting in Qlik Sense. In this case, it can be useful to nest the functions:
Date(Date#(DateInput, 'YYYYMMDD'),'YYYY.MM.DD')
This will interpret the DateInput field according to the input format, YYYYMMDD, and return it in the format you want to use, YYYY.MM.DD.
| Function | Description |
|---|---|
| Date | Date() formats an expression as a date using the format set in the system variables in the data load script, or the operating system, or a format string, if supplied. |
| Date# |
Date# evaluates an expression as a date in the format specified in the second argument, if supplied. |
| Dual |
Dual() combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes. |
| Interval | Interval() formats a number as a time interval using the format in the system variables in the data load script, or the operating system, or a format string, if supplied. |
| Interval# | Interval#() evaluates a text expression as a time interval in the format set in the operating system, by default, or in the format specified in the second argument, if supplied. |
| Money | Money() formats an expression numerically as a money value, in the format set in the system variables set in the data load script, or in the operating system, unless a format string is supplied, and optional decimal and thousands separators. |
| Money# |
Money#() converts a text string to a money value, in the format set in the load script or the operating system, unless a format string is supplied. Custom decimal and thousand separator symbols are optional parameters. |
| Num | Num() formats an expression numerically in the number format set in the system variables in the data load script, or in the operating system, unless a format string is supplied, and optional decimal and thousands separators. |
| Num# |
Num#() converts a text string to a numerical value, in the number format set in the data load script or the operating system. Custom decimal and thousand separator symbols are optional parameters. |
| Text | Text() forces the expression to be treated as text, even if a numeric interpretation is possible. |
| Time | Time() formats an expression as a time value, in the time format set in the system variables in the data load script, or in the operating system, unless a format string is supplied. |
| Time# |
Time#() evaluates an expression as a time value, in the time format set in the data load script or the operating system, unless a format string is supplied. |
| Timestamp | TimeStamp() formats an expression as a date and time value, in the timestamp format set in the system variables in the data load script, or in the operating system, unless a format string is supplied. |
| Timestamp# | Timestamp#() evaluates an expression as a date and time value, in the timestamp format set in the data load script or the operating system, unless a format string is supplied. |