topic 1.1.1753 instead of Null value in QlikView

1.1.1753 instead of Null value

Thu, 16 Sep 2010 19:06:22 GMT

When I load a table from a Pervasive SQL source and ther is no value in a date field for a record then QV stores the value '01/'01/1753' in QVD.

My settings at the beginning of the script are the following:

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET TimeFormat='hh:mm:ss';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
SET Verbatim = 1;

How can I change this behaviour? I like to have instead a NULL value .

Thx.

Ali

1.1.1753 instead of Null value

johnw — Thu, 16 Sep 2010 19:18:57 GMT

That's not just some funny way that QlikView displays NULL. QlikView isn't just making up the 01/01/1753 date from scratch. It's almost certainly coming from a combination of how dates are stored in your source and how you're loading them into QlikView.

So... how are dates stored in your source? How are you loading them into QlikView?

1.1.1753 instead of Null value

Miguel_Angel_Baeyens — Thu, 16 Sep 2010 19:22:50 GMT

Hello Ali,

I've run into that before, and in my case was not a QlikView error, that actually doesn't change the behavior of null values if a nullasvalue, nulldisplay or so is explicitly set, rather than a database "feature" in which the default not null value for a date was 1753 or 1754.

And it was not Pervasive but Microsoft Navision (so my guess is that has not to do with the database itself, but with the environment).

Check your database and odbc connections to see what happens if you load that table in some other application (excel, access, performing a raw query to the database...)

Regards.

1.1.1753 instead of Null value

Clever_Anjos — Thu, 16 Sep 2010 21:30:10 GMT

First, check your ODBC driver documentation.

If doesn´t exists any fix for the issue, why just don´t use an "if" in your LOAD stament?

LOAD

if(date='1.1.1793',null(),date)

FROM

yourquery

Re: 1.1.1753 instead of Null value

Anonymous — Tue, 18 Sep 2012 09:43:03 GMT

Did you find a solution for this?? We have the same problem here.

rgrds

Anita

Re: 1.1.1753 instead of Null value

Anonymous — Tue, 18 Oct 2016 14:33:48 GMT

If you have only one date field you can use an IF statement in your LOAD:

LOAD

if ([YourDateField] = '1753-01-01 00:00:00', null(), [YourDateField]) as YOURDATEFIELD

;

If you have more date fields, you can define a "function" and then use it wherever you need it:

First, define the variable:

SET NullTimeStamp

= if ($1 = '1753-01-01 00:00:00', null(), $1);

Then use it when LOAD:

LOAD

$(NullTimeStamp(YourDateField1)) AS YOURDATEFIELD1,

$(NullTimeStamp(YourDateField2)) AS YOURDATEFIELD2,

$(NullTimeStamp(YourDateField3)) AS YOURDATEFIELD3

;

Regards