Connector: problems with the date format with null values

[#1]

Hello,

I'm using the SAP connector to download datas. There is a field with a date. If the values are filled, QV identifies it as a date and I can change the format to e.g. DD.MM.YYYY.

But if there are no values (the date is null) QV ingores the set date-statement. All values they are filled are set as a date, the null-value is filled with a date, formated in the system format e.g. 0-0-0000, straightened as a text.

A workaround is to load the date like

date(LAEDA,'DD.MM.YYYY') as LAEDA

but this isn't practicable.

Any other ideas?

Greetings

MM

[stephencredmond]

Hi,

I don't know if this will work but what if you set the null interpret value?

set NullInterpret='0-0-0000';

Stephen

[#3]

doesn't work.

The nullinterpret defined a character that schould be interpreted as a null value.

I need a statemant that interpreted a null as a date-value.

[stephencredmond]

Why?

A null is not a date value!

If you need to link it to your calendar then perhaps you could use NullAsValue?

Stephen

[#5]

I don't want to link it.

The reason is if there are null-values QV don't interpreted them as date-values and ignores the set date-format.

If I set the statement where LAEDA >0 then all datas are set into my favorited date format.

Here's a snapshot:

ERSDA is ok because all datas are filled, LAEDA is ignored.

[stephencredmond]

Hi,

I am sorry that I don't fully understand the problem.

Can you not change the date format to re-interpret the different dates?

Set DateFormat = 'YYYY-MM-DD';
Set NullInterpret = '0000-00-00';
Load * inline [
D1
0000-00-00
1995-07-05
1995-07-06
1995-07-20
];
SET DateFormat='DD.MM.YYYY';
Load * inline [
D2
08.07.1995
09.07.1995
13.07.1995
];

Of course I know this example is simplistic.

Stephen

[#7]

I try to explain:

the value "0000-00-00" is an emtpy value (null) in the data. QV is reading the other values, interpreted them as a date-value, format "yyyy.mm.dd" and set the null value to this format.

The other fields, which are full filled as a date, are set into the "set date"-format.

Unfortunately QV don't change the format of the LAEDA-table.

Regards

Martin

[#8]

I found out:

Even the field LAEDA is emtpy in SAP the connector committed a "0000-00-00" as value.

So the field isn't null, it is this value.

So I try to replace "0000-00-00" by '' or 0, this is ok. But Qlikview did not transform the datas from 1995-05-15 to 15.05.1995, set by the parameter settings.

Regards