topic Re: Crosstable load: problem with dates in columns in QlikView

Crosstable load: problem with dates in columns

mazacini — Tue, 03 Jul 2012 18:18:49 GMT

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

Re: Crosstable load: problem with dates in columns

Jason_Michaelides — Tue, 03 Jul 2012 18:31:01 GMT

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

Re: Crosstable load: problem with dates in columns

mazacini — Tue, 03 Jul 2012 20:33:50 GMT

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

Re: Crosstable load: problem with dates in columns

Jason_Michaelides — Tue, 03 Jul 2012 21:59:19 GMT

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;

Re: Crosstable load: problem with dates in columns

mazacini — Wed, 04 Jul 2012 09:49:53 GMT

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

Re: Crosstable load: problem with dates in columns

Jason_Michaelides — Wed, 04 Jul 2012 11:41:22 GMT

Instead of the WHERE clause above, try

WHERE NOT IsText(PayDate)

Re: Crosstable load: problem with dates in columns

mazacini — Wed, 04 Jul 2012 12:14:40 GMT

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

Re: Crosstable load: problem with dates in columns

Jason_Michaelides — Wed, 04 Jul 2012 14:03:07 GMT

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

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

Re: Crosstable load: problem with dates in columns

mazacini — Wed, 04 Jul 2012 17:13:30 GMT

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