QV 10 - date problem with ODBC

[Anonymous]

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??

[suniljain]

you can use if condtion to restrict null value to become date.

Date(If(Len(Date_Filed) >1,Date_Filed))

[Anonymous]

But it seems that QV retrieves a value through the ODBC (the max) instead of a null value ...

Really strange, because 2 days ago with QV9 we never had this problem.

[Miguel_Angel_Baeyens]

Anita,

Have you updated your SQL Server or Navision version or Driver? Deploying on one of our customers, we were working with one version. In the meantime, they upgraded, and the null dates become dates above and below all ranges, so we run into a very similar issue. The fix was to translate in the script with a conditional all dates below the minimum date they told us that was correct into a valid one, and likewise with the max date.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous · Accepted solution]

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 ...

[Miguel_Angel_Baeyens]

Anita,

Although the work around is fine, contact with support@qlik.com and open a support case, so they can confirm whether it is the expected behavior in v10 or something has changed and it is a bug.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica