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 TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';
SET Verbatim = 1;
How can I change this behaviour? I like to have instead a NULL value .
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?
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...)
If you have only one date field you can use an IF statement in your 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:
$(NullTimeStamp(YourDateField1)) AS YOURDATEFIELD1,
$(NullTimeStamp(YourDateField2)) AS YOURDATEFIELD2,
$(NullTimeStamp(YourDateField3)) AS YOURDATEFIELD3