7 Introduction to data modeling
l Fields like “Date”, “Company” and “Name” may exist in several tables and have identical
values, but still have different roles (Order Date/Shipping Date, Customer Company/Supplier
Company). In such cases they should not be used as keys.
l Make sure that redundant fields aren’t used – that only the necessary fields connect. If for example a
date is used as a key, make sure not to load year, month or day_of_month of the same date from
more than one internal table.
l If necessary, form your own non-composite keys, typically using string concatenation inside an
AutoNumber script function.
Data types in Qlik Sense
Qlik Sense can handle text strings, numbers, dates, times, timestamps, and currencies correctly. They can
be sorted, displayed in a number of different formats and they can be used in calculations. This means, for
example, that dates, times, and timestamps can be added to or subtracted from each other.
Data representation inside Qlik Sense
In order to understand data interpretation and number formatting in Qlik Sense, it is necessary to know how
data is stored internally by the program. All of the data loaded into Qlik Sense is available in two
representations: as a string and as a number.
1.
The string representation is always available and is what is shown in the list boxes and the other sheet
objects. Formatting of data in list boxes (number format) only affects the string representation.
2.
The number representation is only available when the data can be interpreted as a valid number. The
number representation is used for all numeric calculations and for numeric sorting.
If several data items read into one field have the same number representation, they will all be treated as the
same value and will all share the first string representation encountered. Example: The numbers 1.0, 1 and
1.000 read in that order will all have the number representation 1 and the initial string representation 1.0.
Number interpretation
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.
The default formats for the different data types are:
l integer, floating point numbers: the default setting for number
l currency: the default setting for currency
l time, date, timestamp: ISO standard formatting
Loading and Modeling Data - Qlik Sense, 1.1 75