How to get this date stored as text as a regular date field in QlikView?

[NenadV]

Hi,

attached is the data source file from which I can't get a regular date field from the DateDMMMYYYY column in QlikView using this standard function or even  date#():

date(DateDMMYYYY,'d.MM.YYYY') as DateProduced;

Any help is very welcome.

Thank you 

[lennart_mo · Accepted solution]

Hi @NenadV,

Going off your sample data I would suggest the following formula:

date(Alt(date#(DateDMMYYYY, 'DDMMYYYY'), date#(DateDMMYYYY, 'DMMYYYY'))) as DateProduced

The date#() function needs the format used in your dataset, the date() function converts that to an actual date, corresponding to the format you set locally.

I used the Alt() as QlikSense seemed to have a problem with the switch between one and two days in the string, I assume so does QlikView. So if there's a non-numeric value returned from an 8 digit date, Qlik automatically resorts to the next format resolving a 7 digit date. You could also solve this by checking the length of the string.

Best regards

Lennart

[NenadV]

Thanks Lennart,

I didn't know about the Alt() function.

Your Formula doesn't work because it ends with this date 30.12.1899 for all values

[NenadV]

But this formula does work well for both dates, with one digit day and two digits day:

if(len(rtrim(DateDMMYYYY)) = 7, date(Alt(date#(DateDMMYYYY, 'DMMYYYY'), date#(DateDMMYYYY, 'DMMYYYY'))), date(Alt(date#(DateDMMYYYY, 'DDMMYYYY'), date#(DateDMMYYYY, 'DDMMYYYY')))) as DateProduced,

So, without your valuable input I wouldn't have solved it. 

Thank you, sir

[lennart_mo]

Okay, glad you got it! 

Don't know why my formula seemed to return all zeros (the numeric value of 30.12.1899) for you, but maybe that's just a difference between Sense and View. Do you use NullAsValue by any chance?

With your new formula you should be able to drop the Alt() though, reducing the total length by quite a bit:

if(len(rtrim(DateDMMYYYY)) = 7, date(date#(DateDMMYYYY, 'DMMYYYY')), date(date#(DateDMMYYYY, 'DDMMYYYY'))) as DateProduced