When you load data containing numbers, currency, or dates, it will be interpreted differently depending on whether the data type is defined or not. This section describes how data is interpreted in the two different cases.
Data with type information
Fields containing numbers with a defined data type in a database loaded using ODBC will be handled by Qlik Sense according to their respective formats. Their string representation will be the number with an appropriate formatting applied.
Qlik Sense will remember the original number format of the field even if the number format is changed for a measure under Number formatting in the properties panel.
To learn more about the properties panel, see Properties panel.
The default formats for the different data types are:
- integer, floating point numbers: the default setting for number
- currency: the default setting for currency
- time, date, timestamp: ISO standard formatting
The default settings for number and currency are defined using the script number interpretation variables or the operating system settings (Control Panel).
For more information, see Number interpretation variables.
Data without type information
For data without specific formatting information from the source (for example, data from text files or ODBC data with a general format) the situation becomes more complicated. The final result will depend on at least six different factors:
- The way data is written in the source database
- The operating system settings for number, time, date and so on. (Control Panel)
- The use of optional number-interpreting variables in the script
- The use of optional interpretation functions in the script
- The use of optional formatting functions in the script
- The number formatting controls in the document
Qlik Sense tries to interpret input data as a number, date, time, and so on. As long as the system default settings are used in the data, the interpretation and the display formatting is done automatically by Qlik Sense, and the user does not need to alter the script or any setting in Qlik Sense.
By default, the following scheme is used until a complete match is found. (The default format is the format such as the decimal separator, the order between year, month and day, and so on, specified in the operating system, that is, in the Control Panel, or in some cases from the special number interpretation variables in the script.
Qlik Sense will interpret the data as:
- A number in accordance with the default format for numbers.
- A date according to the default format for date.
- A timestamp according to the default format for time and date.
- A time according to the default format for time.
- A date according to the following format: yyyy-MM-dd.
- A time-stamp according to the following format: YYYY-MM-DD hh:mm[:ss[.fff]].
- A time according to the following format: hh:mm[:ss[.fff]].
- Money according to the default format for currency.
- A number with '.' as decimal separator and ',' as thousands separator, provided that neither the decimal separator nor the thousands separator are set to ','.
- A number with ',' as decimal separator and '.' as thousands separator, provided that neither the decimal separator nor the thousands separator are set to ‘.'.
- A text string. This last test never fails: if it is possible to read the data, it is always possible to interpret it as a string.
When loading numbers from text files, some interpretation problems may occur, for example, an incorrect thousands separator or decimal separator may cause Qlik Sense to interpret the number incorrectly. The first thing to do is to check that the number-interpretation variables in the script are correctly defined and that the system settings in the Control Panel are correct.
When Qlik Sense has interpreted data as a date or time, it is possible to change to another date or time format in the properties panel of the visualization.
Since there is no predefined format for the data, different records may, of course, contain differently formatted data in the same field. It is possible for example, to find valid dates, integers, and text in one field. The data will therefore, not be formatted, but shown in its original form.