Change column format from General to Date

[qliky88]

Hi Experts, 

I have a column in my data set that Qlik has recognized as General, however I want to convert it to Date. 

The column is in MMM/DD/YYYY hh:mm:tt (e.g. Apr 4 2008 4:00AM) and I want to convert it to DD/MMM/YYYY format. Have tried a few variations of the screenshot attached but to no avail. Any ideas where I'm going wrong? Should I be converting it in the load script? 

Thanks 🙂

[qliky88]

@Kushal_Chawda  of course, it is not something I have immediate access to but I will try to get to the bottom of it and will update in this thread 🙂 Thanks ! 

[qliky88]

@Kushal_Chawda the data source is in varchar format and possibly in different formats for each value.

Is there any workaround for this or does the source data need to be addressed first before analyzing in Qlik ? 

Thanks 🙂 

[Kushal_Chawda · Accepted solution]

Best way to handle this problem is to change the format of the field at database end itself to have a unique format for all the values. If it is not possible then you need to identify all the unique format exists in all the field values and use the alt function to change format for each

Let's assume you identified below two formats in your data

'MMM D YYYY hh:mmTT' ,'DD.MM.YYYY hh:mmTT'

then you can below logic in qlik to convert

date(floor(alt(timestamp#(Date,'MMM D YYYY hh:mmTT'),timestamp#(Date,'DD.MM.YYYY hh:mmTT'))),'DD/MMM/YYYY') as Date

[qliky88]

Thanks @Kushal_Chawda looks like we have some house keeping to do in the database prior to bringing it in to Qlik. Much appreciated ! 😄 

[qliky88 · Accepted solution]

Hi @Kushal_Chawda 

Just for the record a solution was eventually arrived at. Hopefully the below formula may help anyone in the future 🙂 

Date(makedate(num(If(num(subfield("datefield",' ',3))<10, subfield("datefield",' ',4), subfield("datefield",' ',3))), sum(month(date#(subfield("datefield",' ',1),'MMM'))), num(If(subfield("datefield",' ',2)='', subfield("datefield",' ',3),subfield("datefield",' ',2)))) ,'DD MMM YYYY') as NewDateField