Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am loading several tables from a website. They all have the field 'Year' and I am loading them with that field. Unfortunately, some tables load the field as text and others as a number. This is causing me to have "duplicate" years. I need them to all be numbers when loaded. Most of these come in via CrossTables when they get read as text. Below is a snippet of code where they get loaded as text:
CrossTable(Year, FinishTime)
LOAD [ ] as FinishGender,
[1980],
[1995],
[2002],
[2005],
[2010],
[2011],
[2012],
[2013]
FROM
[http://www.runningusa.org/marathon-report-2014?returnTo=annual-reports]
(html, utf8, embedded labels, table is @1, filters(
Hi,
Try with Evaluate() function in your script for the field Year to convert text to number.
Evaluate(Year).
maybe
num#(Year)
Hi,
maybe you could use the Alt() function and list all possible number formats.
hope this helps
regards
Marco
Crosstable() will always interpret your field names as text values. You need to use a second load to interpret your field values as numbers.
See for example this thread for further information:
Hi,
Replace square bracket ( [ ) with double inverted comma ( " ). Something like below,
CrossTable(Year, FinishTime)
LOAD [ ] as FinishGender,
"1980",
"1995",
"2002",
"2005",
"2010",
"2011",
"2012",
"2013"
FROM
Thanks
Hi may be try below.
Temp:
CrossTable(Year, FinishTime)
LOAD [ ] as FinishGender,
[1980],
[1995],
[2002],
[2005],
[2010],
[2011],
[2012],
[2013]
FROM
[http://www.runningusa.org/marathon-report-2014?returnTo=annual-reports]
(html, utf8, embedded labels, table is @1, filters)
Main:
Load *, num#(Year) as New_Year
Resident Temp;
drop table Temp;
Hope it helped.
Regards
ASHFAQ
If nothing of the above works do:
myField * 1 as myField
Simply multiply by 1 ...