timestamp

[#1]

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?

[#2]

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

[#3]

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.

[#4]

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

[hector]

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