Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
GaryMcDonald
Contributor II
Contributor II

Multiple Date format Same field - Qlik Sense

Hello,

I cant seem to find a query that will resolve my issue, if anyone has any suggestions I would be very grateful.

 

I have a date field ([Effective Date]) where the format appears to change between 'dd/mm/yyyy' & 'dd/mm/yyyy hh:mm'.

 

When I load the data only the dates formatted with a timestamp appear - any suggestions as to how I can get all dates to appear?

 

an example attached

 

Many thanks

Gary

Labels (2)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

I loaded your sample data and noticed that the timestamps are loading as strings and the dates as dates.  I used the Alt() function to convert the Effective Date field to a date.

Date(Alt(Date#([Effective Date],'M/DD/YYYY'),Date#([Effective Date],'M/DD/YYYY m:ss')),'MM/DD/YYYY') as [Effective Date]

View solution in original post

2 Replies
jwjackso
Specialist III
Specialist III

I loaded your sample data and noticed that the timestamps are loading as strings and the dates as dates.  I used the Alt() function to convert the Effective Date field to a date.

Date(Alt(Date#([Effective Date],'M/DD/YYYY'),Date#([Effective Date],'M/DD/YYYY m:ss')),'MM/DD/YYYY') as [Effective Date]

GaryMcDonald
Contributor II
Contributor II
Author

Thanks for this - Yes, the dates were mixed between string and numeric. Thanks