topic Re: date tables in Qlik Learning Discussions

date tables

sakamsureshbabu — Fri, 17 Oct 2014 06:56:15 GMT

i have date format like this

9/10/2013

4-10-2014

this two format have in my data

can i load this data to script or do you what to modify data any one format before loading.

jonathandienst — Fri, 17 Oct 2014 06:59:35 GMT

You can do it with something like this (change the field name DateField to match your data):

LOAD

Date(Alt(Date#(DateField, 'D/M/YYYY'), Date#(DateField, 'D-M-YYYY'))) As DateField

HTH

Jonathan

its_anandrjs — Fri, 17 Oct 2014 07:01:33 GMT

Use Alt function for this see the example for this

LOAD Date, Alt( date#( Date , 'DD/MM/YYYY' ),

date#( Date , 'DD-MM-YYYY' ),

) as NewDate;

LOAD * Inline

[

Date

9/10/2013

4-10-2014

];

maxgro — Fri, 17 Oct 2014 07:05:50 GMT

see here (pag 😎 for detail

TIP 8: FIELDS WITH MIXED DATE FORMATS

If you have mixed date format in one field or you have data from different sources using

different formats, you can use the Alt() function to define several possible date formats:

Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

Date#(MixDate,'M/D/YYYY'),

Date#(MixDate,'D/M/YYYY'),

Date#(MixDate,'YYYYMMDD'),

Date#(MixDate,'DD.MM.YYYY'),

Date#(MixDate'YYYY-MM-DD')

) as MixDate

The order of the interpretation functions also defines the priority when several formats are

possible for one specific field value, e.g. 8/4/2012, which in the United States means 4th of

August, but in the United Kingdom means 8th of April.