|Summary:||Instructions on how to determine the max(timestamp) in order to implement incremental load in QlikView.|
|Article Type:||Information / Troubleshooting / Support|
|Related Product(s):||This article relates to the following products:|
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:
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.
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”.
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”.