Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I am a bit confused regarding the alt function. I have the following two text files:
Dates1.txt contains Dates with the format DD/MM/YYYY:
Date
01/01/2015
12/01/2015
21/01/2015
Dates2.txt contains dates with the format MM/DD/YYYY:
Date
04/23/1984
03/06/1984
03/10/1984
When using the alt function, it seems that QlikView is not able to interpret the different date formats correctly. The list box with the field "Date" shows:
06.03.84
10.03.84
23.04.84
01.01.15
21.01.15
01.12.15
The last entry should be 12.01.15. What am I missing?
I know, I can format the Dates separately in the code when loading the text files. But is there a way to use the alt function when loading the "DATA_WITH_ALT" table (see attachment) and get the right results?
Thanks in advance,
Olga
The Alt() function tries to evaluate the arguments and check on numeric return in order of appearance, so if you try
MM/DD/YYYY format first, 12/01/2015 will match this format and will be interpreted as Dec 1st.
If you know the format per file, use an explicite date format parsing per file instead.
DATA.tmp:
LOAD Date#(Date,'DD/MM/YYYY') as Date
FROM
[Data\Dates1.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
LOAD Date#(Date,'MM/DD/YYYY') as Date
FROM
[Data\Dates2.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
//
//DATA_WITH_ALT:
//NoConcatenate LOAD
// Date(ALT(Date#(Date,'MM/DD/YYYY'),Date#(Date,'DD/MM/YYYY'),'No valid date')) as Date_ALT
//RESIDENT DATA.tmp;
//
//DROP Table DATA.tmp;
Dear swuehl,
thanks for your reply.
But what if I am loading both (or more) tables in one step?
LOAD
...
FROM
[Data\Dates*.txt]
How can I assure, that the date format for all tables is the right one?
Best regards,
Olga
Olga,
the problem in your case is that the formats DD/MM/YYYY and MM/DD/YYYY are ambiguous in cases where day of month and month numbers both are <= 12. You can't tell then if 01/12/2015 is Dec 1st or Jan 12th.
If the formats would be unambiguous for all possible values, like when using distinct separators, you could use ALT() function, for example
LOAD
ALT( DATE#( DateField, 'MM/DD/YYYY'), DATE#(DateField, 'DD.MM.YYYY'), 'no valid date') as DateField
FROM
[Data\Dates*.txt];