Cross tables and date format, BUT still loading all columns using LOAD * FROM

[#1]

Hi all,

Previous discussions show how to change the numberical date into date format however this requires expanding the LOAD * FROM. I want to keep the script as a LOAD * FROM because then when new columns are added to the data in Excel, all that is required is to click re-load and not add new columns into the script. This is important because I am developing a QlivView for someone else to use but they will not be learning how to code it.

I have two tables with the common field "Date". One of the tables is a cross table. The script I have for the cross table is:

CrossTable (Date, Participant_Name)

LOAD * FROM



(ooxml, embedded labels, table is [Tab5 xxxxx]);

LOAD date(Date,'MMM-YY') as Date; 

This script seems to recognise that "Date" is from both tables, but it does not match them up. "Date "comming from the normal table is formatted like a date, but data from the cross table come out in the numerical form. For example, there is May 2011 in both date and numerical format. Below is a screen shot of the Date slider to show what I mean.

I have also tried modifying the code as follows:

CrossTable (DateN, Participant_Name)

LOAD * FROM



(ooxml, embedded labels, table is [Tab5 xxxxx]);

LOAD date(DateN,'MMM-YY') as Date; 

When I now look at Date (as a slider), only Date from the normal table is recognised and it simply does not register the data related to Date from the cross table.

Does anyone have suggestions to how I can keep the cross table as a LOAD * FROM and still get the Date to match up and be in date format?

Many thanks,

Isabel

[Gysbert_Wassenaar · Accepted solution]

The crosstable function will turn Date into a string, not a number. What you need to do afterwards is turn it into a date again with the num#() function. Something like this:

Temp:

CrossTable(TempDate,Participant_Name)

load * from ...myexcelfile...;

Result:

Load *, date(num#(TempDate),'DD/MM/YYYY') as Date

resident Temp;

drop field TempDate;

[#3]

Thank you very much. This worked perfectly.