Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sakamsureshbabu
Creator
Creator

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
its_anandrjs

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
MVP
MVP

see here (pag 😎  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.