Hi,
I'm loading in a number of XL files, which have a date format of DD MM YYYY.
I had the script working fine when the XL dates were strings and set to DD MMM YYYY (this is when they were taken directly from an external source and saved as xls files).
We needed to crosstable the source data - and to make the qvw future proof (as the date columns grow in the external XL file) we decided to pack out the columns using XL so that we have date columns way out into the future. This meant we had to change the format of the date columns to a date - hence they changed to the DD MM YYYY format.
In making this change, all the date manipulation script is no longer working (in my preceding load of preceding load)?
I'm guessing it's something to do with Date# and Date. I've tried various combinations but no joy.
Any help greatly appreciated.
Here's the script:
TMP:
CROSSTABLE(WeekStart,AVG_Unit_Price)
LOAD BARCODE,
[25/05/2013],
[01/06/2013],
[08/06/2013],
[15/06/2013],
[22/06/2013],
[29/06/2013],
[06/07/2013],
[13/07/2013],
[20/07/2013],
[27/07/2013],
[03/08/2013],
[10/08/2013],
[17/08/2013],
[24/08/2013],
[31/08/2013],
[07/09/2013],
[14/09/2013],
[21/09/2013],
[28/09/2013],
[05/10/2013],
[12/10/2013],
[19/10/2013],
[26/10/2013],
[02/11/2013],
[09/11/2013],
[16/11/2013],
[23/11/2013],
[30/11/2013],
[07/12/2013],
[14/12/2013],
[21/12/2013],
[28/12/2013],
[04/01/2014],
[11/01/2014],
[18/01/2014],
[25/01/2014],
[01/02/2014],
[08/02/2014],
[15/02/2014],
[22/02/2014],
[01/03/2014],
[08/03/2014],
[15/03/2014],
[22/03/2014],
[29/03/2014],
[05/04/2014],
[12/04/2014],
[19/04/2014],
[26/04/2014],
[03/05/2014],
[10/05/2014],
[17/05/2014],
[24/05/2014],
[31/05/2014],
[07/06/2014],
[14/06/2014],
[21/06/2014],
[28/06/2014],
[05/07/2014],
[12/07/2014],
[19/07/2014],
[26/07/2014],
[02/08/2014],
[09/08/2014],
[16/08/2014],
[23/08/2014],
[30/08/2014],
[06/09/2014],
[13/09/2014],
[20/09/2014],
[27/09/2014],
[04/10/2014],
[11/10/2014],
[18/10/2014],
[25/10/2014],
[01/11/2014],
[08/11/2014],
[15/11/2014],
[22/11/2014],
[29/11/2014],
[06/12/2014],
[13/12/2014],
[20/12/2014],
[27/12/2014],
[03/01/2015],
[10/01/2015],
[17/01/2015],
[24/01/2015],
[31/01/2015],
[07/02/2015],
[14/02/2015],
[21/02/2015],
[28/02/2015],
[07/03/2015],
[14/03/2015],
[21/03/2015],
[28/03/2015],
[04/04/2015],
[11/04/2015],
[18/04/2015],
[25/04/2015],
[02/05/2015],
[09/05/2015],
[16/05/2015],
[23/05/2015],
[30/05/2015],
[06/06/2015],
[13/06/2015],
[20/06/2015],
[27/06/2015],
[04/07/2015],
[11/07/2015],
[18/07/2015],
[25/07/2015],
[01/08/2015],
[08/08/2015],
[15/08/2015],
[22/08/2015],
[29/08/2015],
[05/09/2015],
[12/09/2015],
[19/09/2015],
[26/09/2015],
[03/10/2015],
[10/10/2015],
[17/10/2015],
[24/10/2015],
[31/10/2015]
FROM
[..\ETLFiles\RetailSalesFiles\WaitroseAvgPricePerUnit.xls]
(biff, embedded labels, table is Sheet1$);
MainTable:
LOAD *,
'Waitrose' AS Customer,
MONTH(WeekStartDate) AS Month, //month() returns the 3 character month which can also be sorted numerically
WEEK([WeekStartDate]) AS WeekStartNo,
WEEK([WeekStartDate]) & '-' & WEEKYEAR([WeekStartDate]) AS [WeekYear],
WEEKYEAR([WeekStartDate]) AS WeekStartNoYear_SortOrderForCharts,
YEAR([WeekStartDate]) AS Year,
FLOOR([WeekStartDate]) AS [NUM_Date] //create number version of transaction date so we can join to Calendar
;
//map in the description and line number from the master description / barcode / line number table
LOAD 'Waitrose AVG unit price' AS TransactionType,
BARCODE AS Barcode,
APPLYMAP('DescriptionBarcode_Map', BARCODE, 'No description') AS Description,
DATE(DATE#(WeekStart,'DD MM YYYY'),'DD MM YYYY') AS WeekStartDate, //this is the lowest granularity date we have - i.e. the week commencing date in DD MM YYYY format
AVG_Unit_Price
RESIDENT TMP;
DROP TABLE TMP;
Regards,
