Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Different date formats from different source files

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

Tags (1)
4 Replies
swuehl
Not applicable

Re: Different date formats from different source files

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.

swuehl
Not applicable

Re: Different date formats from different source files

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;

Not applicable

Re: Different date formats from different source files

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

swuehl
Not applicable

Re: Different date formats from different source files

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