topic Dates in Excel 2007 in QlikView

Dates in Excel 2007

Mon, 30 Nov 2009 05:44:24 GMT

I have a problem with date values when transposing an excel table.

A simple sheet like this is used:

A load of this data works fine in Excel 2007:

LOAD Date, Value
FROM
theFile
(ooxml, embedded labels, table is Sheet1);

Then, if I use a file with values like this instead:

This time I make a transpose to put figures in the right place

LOAD Date, Value
FROM
theFile
(ooxml, embedded labels, table is Sheet1, filters(
Transpose()
));

The result will be:

The date field is now impossible to convert to something that looks like a date value.

So I think that I have made something wrong. But I make a last test saving the excel file into 97-2003 format. Then, the same procedure will work - I will get date values correctly.

It is no big deal to save the data into an older format - but is this a bug or a user error?

Dates in Excel 2007

Mon, 30 Nov 2009 12:26:09 GMT

try using below

load date(Date) as Date,Value

from .........

regards

peter

Dates in Excel 2007

Mon, 30 Nov 2009 15:55:01 GMT

Ouch! That was a too narrow solution, apparently. Works fine. Thank you.

Still - it is interesting that there is a variance in behavior using different kinds of versions in Excel.

Dates in Excel 2007

kji — Mon, 30 Nov 2009 18:20:59 GMT

Actually it looks like all you need to do is to apply numberformat date to the field, the value is read correctly but isn't seen automatically as a date for some reason.