topic Re: Mix date formats - cleansing in QlikView

Mix date formats - cleansing

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a date column and my values are in mixed format.

e.g. one value is in format MM/D/YYYY and another is D/MM/YYYY (ie date and month position isn't known). Could this data be cleansed by using any combination of date and date# function?

Re: Mix date formats - cleansing

sunny_talwar — Tue, 26 Apr 2016 16:41:02 GMT

You will need to use Alt() combined with Date#() function to fix the issue.

Date(Alt(Date#(Date, 'FirstFormat'), Date#(Date, 'SecondFormat'), 'RequiredFormat') as Date

Re: Mix date formats - cleansing

Anonymous — Tue, 26 Apr 2016 16:49:08 GMT

Hi Sunny,

thanks for the info. however I kind of had this idea of using Alt function. But I am just wondering is there any way of doing it without ALT function. I mean just by using Date and Date# .. I believe No.

Re: Mix date formats - cleansing

sunny_talwar — Tue, 26 Apr 2016 17:00:07 GMT

Date#() alone won't work because you can only provide one formatting per Date#() function.

Re: Mix date formats - cleansing

Anonymous — Tue, 26 Apr 2016 17:16:27 GMT

Hey ... one more around the same topic.. any idea about the attached scenario .. its mixed format of Date in date and number format

Re: Mix date formats - cleansing

sunny_talwar — Tue, 26 Apr 2016 19:04:41 GMT

Try this:

Date(Alt(Date(DateField), Date#(DateField, 'DD/MM/YYYY'))) as DateField

If this doesn't work, would you be able to share a sample?