topic Re: Different date formats from different source files in QlikView

Different date formats from different source files

Wed, 09 Dec 2015 12:53:03 GMT

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

Re: Different date formats from different source files

swuehl — Wed, 09 Dec 2015 19:39:40 GMT

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.

Re: Different date formats from different source files

swuehl — Wed, 09 Dec 2015 19:50:29 GMT

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;

Re: Different date formats from different source files

Fri, 11 Dec 2015 16:33:45 GMT

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

Re: Different date formats from different source files

swuehl — Sun, 13 Dec 2015 22:47:11 GMT

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];