4 Replies Latest reply: Jan 13, 2011 10:16 AM by Héctor Muñoz RSS

    timestamp

    ataris

      Hello,

      I'm extracting data from a SQL database and to recognise new records for some tables I'm using the timestamp field. I realise this isn't a time but a binary field but there is nothing else I can use. For other transactional tables i have an Entry No. and this works perfectly.

      When I get the max timestamp it returns a field like this 000000000003AA0D. I however cannot filter on this because in SQL the field looks like this 0x00000000003AA0D. So for some reason QlikView takes the 'x' away. I'm using maxstring(timestamp) to get the latest timestamp. If I use MAX it doesnt work.

      So now I have to add in the 'x' to make this work, I saw there is a replace function but this doesn't work.

      Any suggestions anyone?

        • timestamp
          ataris

          I solved this playing around a bit, is a bugger

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           



          LOAD

           

          maxstring(Customers.timestamp) as MaxID

          FROM

          C:\Customer.qvd (qvd)

          ;Let

          MaxID = peek('MaxID',0,CustomerMaxKeyLoad)

          ;SET

          MaxID2 = 0x$(MaxID);

          I then used the second variable to filter the reocrds in the database, probably a better way and if there is please let me know.

          Cheers



          • timestamp
            ivanov

            In QlikView '0100' < '008F' for example. And MaxString isn't working right in this case. But '0x0100' > '0x008F'.

            So i suggest you use sys.fn_varbintohexstr(timestamp) in SQL statement. In this case you'll get correct string with '0x' at the beginning.

              • timestamp

                Hello Evgeny and everyone

                This is very useful information and in my viewpoint a possible bug + error in QlikView not to be able to load timestamp (hex) from an SQL server without truncating important data.

                Am very grateful to Evgeny for pointing this out as I am using the timestamp field from Microsoft Navision SQL to get the maximum date and id to be used for incremental updates on a daily basis. If the timestamp values were missing 0x, I would be getting the wrong maximum dates and ids all the time.

                QLIKTECH - can you please reply to this post or perhaps give an explanation about why this particular case happens?

                Thanks

                Jeanne

              • timestamp

                Hi,

                I don't know if this is going to help you, but there are expression formats for hex values, i did a test with the hex number posted here, but it returns me a number which doesn't look like a date

                See the attached image and make your comments please

                Rgds