Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

1.1.1753 instead of Null value

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

5 Replies
johnw
Champion III
Champion III

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

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
Not applicable
Author

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

rgrds

Anita

Anonymous
Not applicable
Author

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.