Loading dates in string format from .xlsx file

Mon, 23 Nov 2015 13:38:55 GMT

Hello,

I am trying QlikView and I'm having problem loading dates from xlsx file. I hope you will be able to help me.

The example format of data:

ID	DATE
1	30-Jun-2015
2	31-Jul-2015
3	31-Aug-2015
4	30-Sep-2015

I can load exactly the same values to QlikView (great).

The problem is that they are interpreted as Text so after sorting them I get:

DATE
30-Jun-2015
30-Sep-2015
31-Aug-2015
31-Jul-2015

The script at the moment looks like:

LOAD ID,

DATE

FROM

(ooxml, embedded labels);

Can I somehow indicate in the script that the DATE data is a date so it will be later interpreted in QV also as date?

I wouldn't like to change the format of DATE column on the side of excel (it has GENERAL format set).
I tried to use the Properties -> Sort -> Expression to sort the data but it seems I would need to do this in each object where I will use dates...

I will be grateful for tips & links to places where I can learn how to do this in proper way.

Regards,

Andrzej

Re: Loading dates in string format from .xlsx file

Clever_Anjos — Mon, 23 Nov 2015 17:07:27 GMT

LOAD

Date(Date#(DATE,'DD-MMM-YYYY)) as DATE

topic Re: Loading dates in string format from .xlsx file in QlikView

Loading dates in string format from .xlsx file

Re: Loading dates in string format from .xlsx file