Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

sakamsureshbabu
Contributor

date tables

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. 

3 Replies
MVP
MVP

Re: date tables

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: date tables

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

];

MVP
MVP

Re: date tables

see here (pag 8)  for detail

QlikView Date fields

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.

Community Browser