Solved: QV 10 - date problem with ODBC - Qlik Community

Anonymous · ‎2011-09-08

We installed QV10 ... and ran into the following:

Every night we read data from a native database and save this into a QVD file.This proces seems to go well, just like in QV9.

But it seems that for an empty date field QlikView enters a very very large number (the max?) ... when using a date expression while reading the date fields.

The syntax of the reading of the date field is something like:

LOAD *;

SELECT .....

date(<fieldname>) as DateFieldName

FROM .....

If the date field is empty in de database a very large number is shown.

Anyone heard of this problem??

Anonymous · ‎2011-09-08

We are adding the following code to our loading script to fix this:

if(date(DateField) < AddYears(today(),-1000), null(), date(DateField)) as DateField

But it still is strange that QV9 worked correctly and QV10 does this differently ...

View solution in original post

suniljain · ‎2011-09-08

QV 10 , Which version SR1,SR2,SR3 ?.

Anonymous · ‎2011-09-08

SR3 ... the latest

suniljain · ‎2011-09-08

Bug ID : 39679

Description : Getting data from excel file makes QlikView change format date and data

According to qliktech it is corrected in QV 10 SR3 but I think it is still exist.

Regards

Sunil Jain

its_anandrjs · ‎2011-09-08

Hi Sunil,

Is there a way we can find the bug list of qlikview on net.

Regards,

Anand

suniljain · ‎2011-09-08

PFA

Anonymous · ‎2011-09-08

We don't read this data from Excel ... we read this data from a native database (Microsoft Navision).

suniljain · ‎2011-09-08

if your date field do not have proper date details in that case qlikview date function change the format the date field which is received as parameter.

so source is not make sence.

Anonymous · ‎2011-09-08

Could it be that QlikView 10 ODBC fills in a max date(time) when a date field is empty??

The date fields itself where read correctly in the past (QV8.5 / QV9) ... and now only the 'empty' (= null) values in the data are interpreted wrongly ... a max date is filled in.

Not empty dates are correctly shown as date fields ... so I don't think this is the issue.

Also without the date function the values are wrongly read from the datasource ... just did a test with this.

It seems that the ODBC connection functionality is different from QV9.

Anonymous · ‎2011-09-08

The date displayed: 31-12-4294967295 ... in QV 9 this was just an null value.