date() function does not work as expected in Qlik View

[draghici1109]

Hello Community,

I have the following behavior of Qlik view, which I cannot explain:

In the column BEGDA, I have a date field that is displayed both in number format, like 40359, yet also as a date like 31.10.2009. My goal is to display the values of the column BEGDA only in the format DD.MM.YYYY.  The date() function used for displaying the field BEGDA seems to me to work in an unexpected way. 

Questions:

1. - why is this behavior?
2. - how can one change the code so that I get in the column BEGDA only values formatted as DD.MM?YYYY?

Here is the code:

tmp1:
LOAD
PERNR,
BEGDA,
ENDDA,
AEDTM,
MASSN,
MASSG
FROM [..\DATA_00\00-$(vKunde)-$(vSAPType)-PA0000.qvd](qvd);
Concatenate
LOAD
PERNR,
BEGDA,
ENDDA,
AEDTM,
MASSN,
MASSG
FROM [..\DATA_00\00-$(vKunde)-$(vSAPType)-PA0302.qvd](qvd);

MASSNAHMEN1:
LOAD
PERNR,
date(AEDTM) AS AEDTM,
date(BEGDA -1) AS BEGDA,
MASSN & MASSG AS AUSTR_GRUND
Resident tmp1
Where Left(MASSN,1)='C'
;

MASSNAHMEN_Sortiert:
NoConcatenate
Load
PERNR,
date(AEDTM) AS AEDTM,
date(BEGDA) AS BEGDA,

// my DateFormat is set previously like: SET DateFormat='DD.MM.YYYY';AUSTR_GRUND
Resident MASSNAHMEN1
Order by PERNR;

STORE MASSNAHMEN_Sortiert into [..\DATA_00\mAustrittPersisted.qvd](qvd);
STORE MASSNAHMEN_Sortiert into [..\DATA_00\mAustrittPersisted.csv](txt, delimiter is '$(vExpSep)');

[anat]

can you try Alt function to handle multiple date formats in same field

[_Gerardo_ · Accepted solution]

As Anat mentioned, you can use the ALT function to handle multiple date formats in the same field.

Try this:

Alt(Date(BEGDA,'DD.MM.YYYY'),Date#(BEGDA,'DD.MM.YYYY')) as BEGDA

// ALT in this case is using 2 parameters, one for each type of date (number and formatted one).  

In the image you can see the result for each BEGDA ORIGINAL value:

[_Gerardo_ · Accepted solution]

You also asked why this behavior. I'll try to do my best to keep it simple to understand.

It is all about values interpretation from Qlik, I'm pretty sure one date format came from one QVD and the rest from the other QVD.

Let's say for a given field most of values looks like dates (but stored as number)

40319
44368
40105

Let's imagine that for some reason the system store a 0 when there is no date value.
For Qlik is safer to understand the whole content as a number, and if the user force a Date interpretation using the Date function it will convert that 0 to 30.12.1899 right? 🤔
Something like this happened to first QVD.

In second QVD we were lucky🍀, we don't have any 0 values ... so is safe to assume that all those "date looking values" are dates (but also numbers) so Qlik show them as dates as default (but internally those are still numbers too) so Qlik use the format from the script (system date format by default), so in this case all dates looks like DD.MM.YYYY

When you concatenated both QVDs all those values mixed up (and their formats) and for the whole field BEGDA the new interpretation will probably be something like a string 🤔, so in this case if you use Date function Qlik will try to apply it for those values that Qlik understand can be converted to dates in one format (default one), and the rest will show nothing (as you probably you saw in your data).

I hope you understood at least part of what I explained 😅, and that it was helpful to you.

Regards.

[draghici1109]

This was the ultimate solution, the Alt with two Date functions. It seems to me that the reason of this mixture has as reason the fact that I concatenate to tables. It looks like the date BEGDA is formated / stored differently in the two qvd-file that I use for input.

Thank you for the timely and professional response!