Change date format from integer to normal after using crosstab

[#1]

Please help me with the syntax for converting the integer date below to 'MMM-YYYY'. 

CrossTable(ForecastDate, ForecastQty, 3)

LOAD Franchisee,

     Item,

     ForecastCategory,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM

(ooxml, embedded labels, table is FranchiseEquipmentForecast);

Here's the part that is not working:

LOAD

ForecastDate,

date(Date#(ForecastDate,'MMM-YYYY'),'MMM-YYYY') as ForecastDate,

RESIDENT Crosstab;

[its_anandrjs]

Try before the cross table provide any sample files for the same.

Regards

Anand

[MarcoWedel]

Left Join

LOAD distinct

      ForecastDate,

       date(ForecastDate,'MMM-YYYY') as ForecastDate2

Resident Crosstab;

[#4]

Heres a sample of the file I'm importing using Cross Table:

[#5]

Hi Marco.  Tx for the help.  I still get an error on you script:  "Table not found".

I've added a sample of the file on Anand's reply if that will help

tx

[kavita25]

Hi,

Check the given QVW and excel as per your given picture.

I think, it can solve your issue.

Regards,

Kavita

[its_anandrjs]

Can you provide it in excel format.

Regards

Anand

[#8]

Heres a sample of the file:

[#9]

Hi Kavita, can you paste the script in the QVW file, I have personal edition, so I can't open it this side.  Many thanks

[its_anandrjs · Accepted solution]

Hi,

Then load another resident table and do transformation for the  ForecastDate because it is not in date format first convert it into date format and then Date format to achieve the result and format date as 'MMM-YYYY'.

Crosstab:
CrossTable(ForecastDate, ForecastQty, 3)

LOAD Franchisee,
Item,
ForecastCategory,
[42005],
[42036],
[42064],
[42095],
[42125],
[42156],
[42186],
[42217],
[42248],
[42278],
[42309],
[42339]
FROM
FranchiseEquipmentForecast_2015_Sample.xlsx
(ooxml, embedded labels, table is FranchiseEquipmentForecast);

LOAD  ForecastDate,
Date(Num#(ForecastDate,'MMM-YYYY'),'MMM-YYYY') as ForecastDateNew
Resident Crosstab;

Regards

Anand