topic Re: Using crosstable from excel and converting dates in QlikView

Using crosstable from excel and converting dates

Mon, 30 Jun 2014 17:04:04 GMT

Hello Everyone,

This might be really simple but I have a problem, in excel I have a spreadsheet like this:

REG QUESTION 6/16/2014 5/16/2014 4/16/2014 3/16/2014

TX Yes 80 75 72 80

TX No 10 10 10 10

TX Maybe 10 15 18 10

FL Yes .. .. .. ..

FL No .. .. .. ..

FL Maybe .. .. .. ..

So, I'm using crosstable to convert dates into new field Dates and Score field as Data Field as Score. However in QV I'm getting wrong format for date such as 41438, 41468 and so on. is there a way to get the right format?

Also is there a way to import only last 4 weeks data from max of Week?

Re: Using crosstable from excel and converting dates

hic — Mon, 30 Jun 2014 19:13:09 GMT

You need to do it two passes:

Data1:

Crosstable (Date, Score, 2)

Load * From ….

MinMaxDate:

Load Max(Date) as MaxDate Resident Data1;

Let vMaxDate = Round(Peek('MaxDate',0,'MinMaxDate'));

Data2:

Noconcatenate

Load REG, QUESTION,

Score,

Date(Num#(Date)) as Date

Resident Data1

Where Date > $(vMaxDate) - 28;

Drop Table Data1;

HIC

Re: Using crosstable from excel and converting dates

Mon, 30 Jun 2014 19:26:44 GMT

Hi Henric,

I tried your code but it still shows date as 41438, 41468,41499... Any idea?

Thanks.

Re: Using crosstable from excel and converting dates

hic — Mon, 30 Jun 2014 19:34:45 GMT

First, I forgot a prefix "Noconcatenate". Add that and try again. (See previous post - I corrected it.)

Secondly, make sure that you drop the second table.

HIC

Re: Using crosstable from excel and converting dates

Mon, 30 Jun 2014 19:44:03 GMT

Data1:

CrossTable(Date, Score, 2)

Load Div,

Question,

[41438],

[41468],

[41499]

From

Data2:

Noconcatenate Load Div,

Question,

Score,

Date(Date) as Date

Resident Data1;

Drop Table Data1;

HI Henric,

Let's forget the 4 weeks data first. I did the above code but it is still showing the wrong format for date. Any ideas?

Thanks.

Re: Using crosstable from excel and converting dates

hic — Mon, 30 Jun 2014 19:48:50 GMT

Yes, there is one more additional thing... I forgot.

The field names look like numbers, but are not interpreted as numbers. So, you need to have a Num#() function to force interpretation as number. See script above...

HIC