Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
Try before the cross table provide any sample files for the same.
Regards
Anand
Left Join
LOAD distinct
ForecastDate,
date(ForecastDate,'MMM-YYYY') as ForecastDate2
Resident Crosstab;
Heres a sample of the file I'm importing using Cross Table:
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
Hi,
Check the given QVW and excel as per your given picture.
I think, it can solve your issue.
Regards,
Kavita
Can you provide it in excel format.
Regards
Anand
Heres a sample of the file:
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
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