Crosstable load: problem with dates in columns

[mazacini]

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

(biff, embedded labels, table is detail$)
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

[Jason_Michaelides]

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

[mazacini]

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

[Jason_Michaelides · Accepted solution]

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;

[mazacini]

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

[Jason_Michaelides]

Instead of the WHERE clause above, try

WHERE NOT IsText(PayDate)

[mazacini]

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

[Jason_Michaelides]

Well spotted about the Date#!  Maybe count the slashes instead:

WHERE SubStringCount(PayDate,'/') <> 2.

[mazacini]

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