1.1.1753 instead of Null value

[#1]

Hi

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

[johnw]

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?

[Miguel_Angel_Baeyens]

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.

[Clever_Anjos]

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

[Anonymous]

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

rgrds

Anita

[Anonymous]

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

J.