Using crosstable from excel and converting dates

[#1]

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?

[hic · Accepted solution]

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

[#3]

Hi Henric,

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

Thanks.

[hic]

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

[#5]

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.

[hic]

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