QlikView: Handling SQL timestamp/rowversion fields

Qlik Technical Article

Article sections

    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:

    • QlikView

    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.

    8 byte binary value

    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

    Convert the Timestamp

    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,

    Convert the timestamp to an integer

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

    Related Information:
    in QlikviewSupportTroubleshooting
    Share This Post
    Share on facebook
    Share on linkedin
    Share on twitter
    Share on email
    More To Explore

    Ready to Talk about your Project?
    Book a free Discovery Call

    A no obligation call to discuss your business needs and project requirements. During the call, our consultant will explain how we can help you to achieve your goals and answer any questions you may have.