If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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?
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
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
Thanks everyone for their replies,
@Kushal_Chawda was the closest to getting it. I inserted your formula into the load script but it seems to now only return one date?!
Very unusual error if anyone could point me in the right direction I would really appreciate it !
Assuming you have put down the formula in load script correctly. If so I don't think any chances of getting error. It might be that all time field values don't have same format due to which only few are getting converted
date(floor(timestamp#(YourTimeField,'MMM D YYYY hh:mmTT')),'DD/MMM/YYYY') as Date