topic Re: Date#() function in QlikView

Date#() function

Thu, 13 Sep 2012 09:27:37 GMT

Guys,

Basically I'm loading data from an excel file and one of the fields is a date field. It mostly has dates in the form 'DD/MM/YYYY' however excel sometimes automatically reduces the field to the form 'D/M/YYYY'.

Hence when i use the function DATE#(String,'DD/MM/YYYY') it doesnt correctly convert dates which are in the 'D/M/YYYY' form

Any ideas?

Faisal

Re: Date#() function

Miguel_Angel_Baeyens — Thu, 13 Sep 2012 09:35:19 GMT

Hello Faisal,

That should work just fine. Take a look at this code:

=Date(Date#('3/9/12', 'DD/MM/YYYY'))

Even not using the leading zeroes for day and month and the century, the date returned is correct.

Hope that helps.

Miguel

Re: Date#() function

Thu, 13 Sep 2012 09:48:59 GMT

Thanks Miguel,

I've tried it but it doesnt work for some reason.

I have attached a sample xls and qvw file to show you.

Faisal

Re: Date#() function

Miguel_Angel_Baeyens — Thu, 13 Sep 2012 10:14:30 GMT

Hi,

Yes, if you use the wizard you will see that there are dates with the format "DD/MM/YYYY" but there are "dates" that excel recognized but QlikView does not with the format "DD-MMM-YYYY" or just numeric "40100". You will see that at a glance when you just open the file and see how some values are right aligned (numeric correct date formatted) and some are left aligned (string date, literal).

You will need to use the Alt() function then with all possible conditions:

LOAD Trader, 
  Date(Alt(Date#([Opening Date], 'DD/MM/YYYY'), [Opening Date], 'Invalid date')) as date
FROM
Date#.xlsx
(ooxml, embedded labels, table is Sheet1);

Hope that helps.

Miguel