Crosstable Question

[#1]

Hi There,

I was working on some date transformations and while busy getting help I discovered the CrossTable function. This saves me a lot of hassle on the excel side of the house. But, when I try to use the crosstable function I can't get the date transformations to work (eg Date(orderdate) as Date) I don't know how to incorporate this in with the Crosstable function. i couldn't find any help online. See my work below. Would appreciate any help on this.

Directory;
CrossTable

(orderdate, Data, 5)
LOAD

Market,
Technology,
Measure,
Geography,
Supply,
[201211],
[201212],
[201301],
[201302],
[201303],
[201304],
[201305],
[201306],
[201307],
[201308],
[201309],
[201310]

FROM

[Crosstable Effort.xlsx]

(ooxml, embedded labels, table is Sheet1);

Date(orderdate) as Date,
Month(orderdate) as Month,
Year(orderdate) as Year,
'Q' & (mod(Ceil((Month - 2) / 3), 4) + 1) as Quarter

[MayilVahanan]

Hi

Try like this

EffortTemp:

CrossTable (orderdate, Data, 5)
LOAD

Market,
Technology,
Measure,
Geography,
Supply,
[201211],
[201212],
[201301],
[201302],
[201303],
[201304],
[201305],
[201306],
[201307],
[201308],
[201309],
[201310]

FROM

[Crosstable Effort.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load
Date(orderdate) as Date,
Month(orderdate) as Month,
Year(orderdate) as Year,
'Q' & (mod(Ceil((Month - 2) / 3), 4) + 1) as Quarter,

Data,

Market,
Technology,
Measure,
Geography,
Supply

Resident EffortTemp;

Drop table EffortTemp;