Comparison of decimal or timestamp values do not work

[ToniKautto]

Two identical timestamps are compared in QlikView, but the logical operation indicates that the value are not equal.

 

In QlikView, a properly formatted timestamp is stored as a decimal value. The visual timestamp is represented by an underlying decimal value. By using the Num() function and expanding all available decimal values it is possible to see that the numerical values are different.

 

In QlikView, all decimal values are stored as floating-point values, which by definition are not the exact values. This means that a floating-point value can always fluctuate in value on the last decimal. The fluctuation can occur after any operation or processing of the value, including the actual load from the data source. 

 References to more details about floating-point values:

• Wikipedia.org http://en.wikipedia.org/wiki/IEEE_floating_point
• IEEE 754 Standard http://ieeexplore.ieee.org/xpl/mostRecentIssue.jsp?punumber=4610933

Resolution:

The easiest way is to compare the actual timestamp text instead of the underlying decimal value. This can be done by using the Text() function. 

QlikView is optimized for processing of numerical values, so the text comparison could possibly give a negative performance impact on large data quantities with many distinct values. In such case it could be better to try and perform the comparison with integer values. This can be done by multiplying the decimal value and thereafter using Floor() to remove the decimal part of the value.