topic QlikView vs SQL Server Date in QlikView

QlikView vs SQL Server Date

Fri, 29 Oct 2010 12:17:20 GMT

I am in the process of writing a generic script for incrementally loading and updating qvd files based on a date field and I have come accross some unusual behaviour in the way QlikView and SQL Server convert numeric dates.

MaxLoad:

LOAD max(ModifyDate) as MaxF

FROM $(vLoadFile) (qvd);

Let vMAX = peek('MaxF', 0, 'MaxLoad');

'$(vDTQVD)':

Load

SQL SELECT *

FROM $(vTable) WHERE $(ModifyDate) > $(vMAX);;

'$(vDTQVD)':

LOAD * FROM $(vfLoadFile)(qvd)

WHERE NOT EXISTS ( [$(vIDField)] );

When I started to run this code against a changing data set, noticed that I was dropping records and on debugging found the following behaviour:-

vMAX = 40408.194

SQL Server interprets this as

SELECT CAST(40408.194 AS DATETIME) FROM vTable returns 2010-08-20 04:39:21.600

but QlikView returns 2010-08-18 04:39:21.600

Any feedback would be fantastic as the numeric method feels so much cleaner than formating the dates as text.

Isabel

QlikView vs SQL Server Date

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 12:26:04 GMT

Hello Isabel,

Indeed, QliKView internally uses date format counting since 31/12/1899 (1) and so on. Since some timestamp include time and date, there you have those decimals in the QlikView date. This will likely help

LOAD Ceil(max(ModifyDate)) as MaxFFROM $(vLoadFile) (qvd);

Anyway, a precedent load script would help, so the dates are stored as integers in your QVD files:

Load Date(DateField, 'DD/MM/YYYY') AS DateField;SQL SELECT *FROM $(vTable) WHERE $(ModifyDate) > $(vMAX);

When you store it now, all values will be properly formatted. You can duplicate fields should you want to keep dates and times in one field but use the other to storeing and loading purposes.

Hope that helps.

QlikView vs SQL Server Date

Fri, 29 Oct 2010 12:42:15 GMT

Hi Miguel,

Thanks for the quick response, knowing that QlikView's date starting point is different to SQL Server means I can adjust the date by adding 2.

Thanks again!

Isabel