topic QV 10 - W in QlikView

QV 10 - date problem with ODBC

Anonymous — Thu, 08 Sep 2011 06:49:14 GMT

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

QV 10 - W

suniljain — Thu, 08 Sep 2011 07:08:04 GMT

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

QV 10 - W

Anonymous — Thu, 08 Sep 2011 07:29:32 GMT

SR3 ... the latest

QV 10 - W

suniljain — Thu, 08 Sep 2011 08:02:57 GMT

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

QV 10 - W

its_anandrjs — Thu, 08 Sep 2011 08:05:05 GMT

Hi Sunil,

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

Regards,

Anand

Re: QV 10 - W

suniljain — Thu, 08 Sep 2011 08:06:38 GMT

PFA

QV 10 - W

Anonymous — Thu, 08 Sep 2011 08:10:17 GMT

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

QV 10 - W

suniljain — Thu, 08 Sep 2011 08:15:42 GMT

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.

QV 10 - W

Anonymous — Thu, 08 Sep 2011 08:22:00 GMT

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.

QV 10 - W

Anonymous — Thu, 08 Sep 2011 08:28:34 GMT

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

QV 10 - W

suniljain — Thu, 08 Sep 2011 08:37:29 GMT

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

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

QV 10 - date problem with ODBC

Anonymous — Thu, 08 Sep 2011 08:54:17 GMT

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.

QV 10 - date problem with ODBC

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 09:17:29 GMT

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

QV 10 - date problem with ODBC

Anonymous — Thu, 08 Sep 2011 09:32:00 GMT

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

QV 10 - date problem with ODBC

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 09:56:02 GMT

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