Numeric values not the same in QlikView as in source data
Article Number: 000005513 | Last Modified: 2019/01/17
Numeric data loaded to QlikView does not get the exact same value as the source data. The symptom appears as any of the following;
In the table below the data source value is in the first column. The loaded values are in the second and third columns.The text component of loaded values remain visually the same as the source data, but the underlying numeric value is not identical to the source data. NOTE: The numeric value is displayed with 14 decimals to visualize the entire content.
- Decimal part of value is cut off
- Decimal part is rounded
- Integer value is stored as text
- Decimal value is stored as text
- Table sum is not as expected
QlikView has a built in limitation of max 14 digits in the integer part and max 14 digits in the decimal part of a numeric value. In total a numeric value is only allowed to contain 17 digits in both integer and decimal parts.
Values with more than 14 digits in the integer part will be loaded as text. This means that the value can be presented in a QlikView application, but it can not be included in any calculation since it has no underlying numeric value.
Decimal parts longer than 14 digits will be cut off. If the values has more than 17 digits in total, the decimal will be cut off so that a 17 digit value is created.
The cut off can lead to an unexpected round, as part of how floating point values are handled. Floating point values are by definition not exact values, and during calculations the last decimal digit can get a variation. This is according to IEEE standards on how to implement floating point values.
In this context please consider all values in QlikView as dual values. This means that the limitation is in the underlying numerical value and not in the textual presentation. When the limits are reached as described above, the underlying value is adjusted, while the text component remains as the original value.
The individual values can be adapted to have a textual presentation that fits the application purpose or the limitation.This can be achieved in the load script with either Round() or Num().Round() will round the actual numeric values and set the text presentation to the same value. The exactness of the underlying numeric value is decreased due to the rounding.Num() will only reformat the text component of the values, hence the underlying data will still be as exact as possible.In all cases the values are still floating point values and there can be a rounding in the last decimal value due to applied calculations during function calls, which also includes the Round() function as can be seen in the above examples. The floating point last decimal rounding also can occur on the table sum, as can be seen in the example above. This can not be resolved in any other way than limiting the numerical format in the chart properties.