Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Handling multiple date formats in one load file

I've got a file with two date formats in it and I'd like to convert it to one format.  I've copied some sample data below.  As you can see, some of the dates are DD-MM-YYYY and some are D/M/YYYY.  I'd like to format the date as MM/DD/YYYY, keep the timestamp format and also be able to create a separate field that is just the date in MM/DD/YYYY format.

 

31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
31-01-2017 12:00:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1/2/2017 0:00
1 Solution

Accepted Solutions
Highlighted

May be this

Date(Floor(Alt(Num(DateField), Num(Date#(DateField, 'DD-MM-YYYY hh:mm:ss')), Num(Date#(DateField, 'D/M/YYYY h:mm')))), 'MM/DD/YYYY') as NewDateField

View solution in original post

3 Replies
Highlighted

May be this

Date(Floor(Alt(Num(DateField), Num(Date#(DateField, 'DD-MM-YYYY hh:mm:ss')), Num(Date#(DateField, 'D/M/YYYY h:mm')))), 'MM/DD/YYYY') as NewDateField

View solution in original post

Highlighted
Creator III
Creator III

Very nice, that worked great!

Thank you.

Highlighted
Contributor II
Contributor II

date(alt(date,Date#(date,'DD-MM-YYYY'),Date#(date,'MM-DD-YYYY'),Date#(date,'DD/MM/YYYY'),Date#(date,'MM/DD/YYYY')),'MM/DD/YYYY') as newdates,