Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Crosstable load with dates

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Crosstable load with dates

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;

2 Replies
giakoum
Honored Contributor II

Re: Crosstable load with dates

SET DateFormat='DD.MM.YYYY';

include this before the load statement

MVP
MVP

Re: Crosstable load with dates

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;

Community Browser