You can edit a table that was added to the app in the Data manager overview, to rename the table or make field transformations.
- Click @ on the data table you want to edit to open the table editor.
Renaming a table
When you add a table in Data manager, the table is assigned a default name, based on the name of the database table, data file, or Excel worksheet, for example. If the name is non-descriptive or unsuitable, you can rename it.
Do the following:
- Click on the table name.
- Edit the table name.
- Press Enter or click outside the table name.
Renaming a field
You can rename fields in a table, either to get a better name that is easier to understand, or to associate the field with a field in another table. Fields with the same name are automatically associated.
Do the following:
- Click on the field name that you want to rename, or select Rename from the drop-down on the row below the field name.
-
Type the new name.
Information noteField names must be unique to each table. If you change the field name to be the same as a field in another table, the tables will automatically associate by that field. - Press the Enter key, or click outside the field.
The field is now renamed.
See: Fields
See: Managing data table associations
Changing field type and display format
When data is added, Qlik Sense automatically interprets the field type of each field. The following field types are currently supported:
- ⏪ General
- G Date
- õ Timestamp
If the data was not interpreted correctly as a date or a timestamp, you can adjust the input format. You can also change the display format of a data or timestamp field.
It is not possible to change field type or display format of fields in some cases.
- The table is the result of concatenating two or more tables.
- The field is already recognized as a date or a timestamp.
Making sure a date or timestamp field is recognized correctly
If a date or timestamp field is not recognized as a date or a timestamp, that is, it is marked with ⏪ General, you can make sure it is interpreted correctly.
Do the following:
-
Click on ⏪ below the field heading.
The data format dialog opens.
- Change Field type to Date or Timestamp.
-
Change the format string in Input format to interpret the date correctly. You can use a prepared format from the drop down list, or write your own format string.
See: Conventions for number and time formats
Information noteIt is not possible to use a single quote in the format string. -
If you want to use a display format other than the default format in your app, write or select a format string in Display format.
If you leave it empty, the app default display format is used.
- Click outside the data format dialog to close the dialog and update the field.
Changing the display format of a date or timestamp field
Each app has default display formats for date and timestamp fields. You can change the display format for an individual date or timestamp field.
Do the following:
-
Click on G (date) or õ (timestamp) below the field heading.
The data format dialog opens.
-
Change the format string in Display format. Either use a prepared format from the drop down list, or write your own format string..
- Click outside the data format dialog to close the dialog and update the field.
Updating a table from the data source
You may want to change the selection of fields from the data source. For example, you may need to add a field that was left out, or the data source may have been updated with added fields. In this case, you can update the table from the data source.
Do the following:
-
Click Select data from source.
The data selection wizard opens with your current selections.
-
Make the required changes in selection.
- Click Load and finish.
The table is now updated with fields according to the selections you made.
Adding a calculated field
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.
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. |
Undo and redo actions
You can undo and redo your table edit actions by clicking B and C.
The undo/redo history is cleared when you close the table editor.