topic Re: Crosstable load with dates in QlikView

Crosstable load with dates

Wed, 17 Oct 2012 11:39:18 GMT

Hi,

I am trying to load data from excel by using the crostable load function. My problem is that the dates are not pulled through as dates but as numbers, How do I make it pull through as dates?

the load statement is...

FORECAST:

crosstable (Date, Units)

LOAD

FROM

[.\Prepped Files\Forecast\Forecast May2012.xlsx]

(ooxml, embedded labels, table is Sheet1);

I have attached a sample of the data I am tying to import.

Thanks

Re: Crosstable load with dates

giakoum — Wed, 17 Oct 2012 11:46:54 GMT

SET DateFormat='DD.MM.YYYY';

include this before the load statement

Re: Crosstable load with dates

swuehl — Wed, 17 Oct 2012 11:46:57 GMT

The CROSSTABLE Load will transform your Date headers into text values, you need a second load to re-interprete as Date numeric values:

FORECAST_TMP:

crosstable (Date, Units)

LOAD

FROM

[.\Forecast May2012.xlsx]

(ooxml, embedded labels, table is Sheet1);

FORECAST:

NoConcatenate LOAD

Barcode,

Date(num#(Date)) as Date,

Units

Resident FORECAST_TMP;

drop table FORECAST_TMP;