QlikView: Handling SQL timestamp/rowversion fields

Issue

Some SQL databases have a field to identify when the row was created or last updated and uses the SQL data type of “timestamp” or “rowversion”.  The data type of the field in earlier versions of SQL was “timestamp” but has now become “rowversion”.  The field is set as a consecutive unique value every time the row is updated (and despite the implication of former naming is not a date).  The value posted is actually an 8-byte binary value which is not easy to interpret.

Also, when this field is loaded into QlikView the first two characters are stripped off, resulting in values like:

0000000000670E1E

0000000000670E1F

000000000066DAE2

000000000066DAE3

000000000066DAE4

This is problematic if you want to determine the max(timestamp) in order to implement incremental load, as the max function on the stripped values will not return a useable value.

Solution

Option 1

Convert the timestamp to an integer using SQL function before the field is loaded into QlikView.

cast(Stamp as bigint) as TimestampAsInt

The function “max” can be used against the new field “TimestampAsInt”.

Option 2

Convert the timestamp to an integer within the QlikView portion of the script.

num(num#(right(Stamp,12), ‘(HEX)’)) as timestampashex,

The function “max” can be used against the new field “timestampashex”.