Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have created a crosstable load for March data as follows:
CrossTable(PayDate, Qty, 3)LOAD [Class type],
Item,
Price,
[01/03/2012],
[02/03/2012],
[03/03/2012],
[04/03/2012],
[05/03/2012],
[06/03/2012],
[07/03/2012],
[08/03/2012],
[09/03/2012],
[10/03/2012],
[11/03/2012],
[12/03/2012],
[13/03/2012],
[14/03/2012],
[15/03/2012],
[16/03/2012],
[17/03/2012],
[18/03/2012],
[19/03/2012],
[20/03/2012],
[21/03/2012],
[22/03/2012],
[23/03/2012],
[24/03/2012],
[25/03/2012],
[26/03/2012],
[27/03/2012],
[28/03/2012],
[29/03/2012],
[30/03/2012],
[31/03/2012]
FROM
(
where len(Item)>0;
Question 1 - How do I get the dates to load as values?
Question 2 - I want to be able to save the April file under the same name into the same directory. Can anyone suggest how I could change my script to be able to load the data for each day of the month irrespective of whether the month has 28,29,30 or 31 dates (and therefore columns).. (Note there is always a Total column to th eimemdiate right of the last column in each month.
Rgds
Joe
Looks like a preceding load won't work with CrossTable(). No matter - try this:
Data_temp:
CrossTable(PayDate,Quantity,3)
LOAD * FROM
(ooxml, embedded labels, table is Sheet1);
Data:
NoConcatenate
LOAD
[ClassType],
Item,
Price,
Date#(PayDate,'DD/MM/YYYY') AS PayDate,
Quantity
RESIDENT Data_temp
WHERE PayDate <> 'Total'
;
DROP TABLE Data_temp;
Can't test it now but a preceding load might work:
Data:
LOAD
ClassType,
Item,
Price,
Date#(PayDate,'DD/MM/YYYY') AS PayDate,
Quantity
WHERE PayDate <> 'Total'
;
CrossTable(PayDate,Quantity,3)
LOAD * FROM...*.xls file;
Hope this helps,
Jason
Hi Jason
Thank you for your respionse.
Getting error message below (PayDate field now FltDate)
Field not found - <FltDate>
CrossTable(FltDate, Qty, 3)
LOAD *
FROM etc etc
Any ideas?
Rgds
Joe
Looks like a preceding load won't work with CrossTable(). No matter - try this:
Data_temp:
CrossTable(PayDate,Quantity,3)
LOAD * FROM
(ooxml, embedded labels, table is Sheet1);
Data:
NoConcatenate
LOAD
[ClassType],
Item,
Price,
Date#(PayDate,'DD/MM/YYYY') AS PayDate,
Quantity
RESIDENT Data_temp
WHERE PayDate <> 'Total'
;
DROP TABLE Data_temp;
Hi Jason
That worked perfectly.
Thanks for your help.
If I could ask you a related question:
Sometimes there are other (non-date) column to the right of the date columns.
Is there a way to exclude any such columns (in the WHERE statement?).
Perhaps exclue on the basis that they are text rather than date?
Anyway, thanks again for your help so far.
Rgds
Joe
Instead of the WHERE clause above, try
WHERE NOT IsText(PayDate)
Hi Jason
That didn't work. No records load into the Data table.
I suspect that it may be due to the fact that PayDate iis a text field, which is only translated by the Date# during the Load?
Maybe there is a way to delete text values for the PayDate field after the load using something like WHERE Not IsText?
Anyway, you have done more than enough, so I will understand if you are too busy to persist with this.
Rgds
Joe
Well spotted about the Date#! Maybe count the slashes instead:
WHERE SubStringCount(PayDate,'/') <> 2.
Hi Jason
If I replace yuor <> with = in a straight WHERE (ie no NOT), then it works perfectly.
Go a lot of different stuff from this post.
Thanks for everything!
Joe