topic Re: Handling 'December 05, 2015' - like Excel date in QlikView

Handling 'December 05, 2015' - like Excel date

Mon, 07 Dec 2015 14:04:31 GMT

Hi,

I have been desperately trying to load a date field from XLS. Its format goes like:

From Date

January 12, 2015

January 19, 2015

January 26, 2015

February 2, 2015

February 9, 2015

February 16, 2015

February 23, 2015

I need to get it in this format in QV:

05/01/2015

09/02/2015

23/03/2015

...

None of my attempts was successful (null values are returned 😞

Num(Date#([From Date], 'MMM DD, YYYY')) AS NumericDate,
Date(Num#([From Date]),'DD/MM/YYYY') as attempt,
Date(Num#([From Date]), 'DD/MM/YYYY') as date3,
Week(Num#([From Date])) & '' as weekTest,
Date(Date#(Num([From Date]), 'MMM DD, YYYY'),'DD/MM/YYYY') as date4,
Date#(Num#([From Date]), 'MMM DD, YYYY') as date6,
Date(Date#(Num#([From Date]), 'MMM DD, YYYY'), 'DD/MM/YYYY') as date8,
Date(Date#('September 24, 2007', 'MMM DD, YYYY'),'DD/MM/YYYY') as date5 // works perfectly

Can you please help? Dates have always been my nightmare

Many thanks.

Re: Handling 'December 05, 2015' - like Excel date

m_woolf — Mon, 07 Dec 2015 18:53:34 GMT

If the dates are really dates in Excel, not text; you can just load the field. QV should recognize the date and format it according to the Set DateFormat command.

Re: Handling 'December 05, 2015' - like Excel date

Kushal_Chawda — Mon, 07 Dec 2015 18:58:32 GMT

try

date(date#([From Date],'MMMM DD YYYY'),'DD/MM/YYYY') as From_Date

Re: Handling 'December 05, 2015' - like Excel date

Kushal_Chawda — Mon, 07 Dec 2015 18:59:54 GMT

if possible share the excel file with sample data

Re: Handling 'December 05, 2015' - like Excel date

sunny_talwar — Mon, 07 Dec 2015 19:16:04 GMT

Try what Kush141087 has provided. Since you date include the full month name, MMM most likely won't work, but MMMM should work.

So Date#([From Date],'MMMM DD YYYY') is telling it to understand January 12, 2015 and Date(,'DD/MM/YYYY')) to convert the date to your required format.

Re: Handling 'December 05, 2015' - like Excel date

m_woolf — Mon, 07 Dec 2015 19:36:50 GMT

https://community.qlik.com/thread/108288

Re: Handling 'December 05, 2015' - like Excel date

Tue, 08 Dec 2015 10:19:05 GMT

Thank you folks. The above solution did not work though. Tried as:

WEEKS:
LOAD
[From Date] as Original,
Date(Date#([From Date],'MMMM DD, YYYY'),'DD/MM/YYYY') as fullNameMonhtsWithComma,
Date(Date#([From Date],'MMMM DD YYYY'),'DD/MM/YYYY') as fullNameMonhtsWoComma
FROM
$(vDataFile)
(ooxml, embedded labels, table is Weeks);

Result:

Have not checked the solution provided by Mike Woolf yet but it looks promising.

Re: Handling 'December 05, 2015' - like Excel date

Tue, 08 Dec 2015 11:13:48 GMT

Mhm, weirdly enough not even this solution worked:

Converting text to date format while importing from web

I wonder whether sth could be broken in XLS metadata? Hence sharing the source file - PFA.