Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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
(
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
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;
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;
Thank you very much. This worked perfectly.