Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jag7777777
Contributor III
Contributor III

Date problem

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,

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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;

View solution in original post

3 Replies
sunny_talwar

Try this:

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;

jag7777777
Contributor III
Contributor III
Author

Perfect thanks sunindia

....should've seen that myself.

Cheers again.

sunny_talwar

No problem

I am glad I was able to help.

Best,

Sunny