Converting text to number in Load

[#1]

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(

[#2]

Hi,

Try with Evaluate() function in your script for the field Year to convert text to number.

Evaluate(Year).

[maxgro]

maybe

num#(Year)

[MarcoWedel]

Hi,

maybe you could use the Alt() function and list all possible number formats.

hope this helps

regards

Marco

[swuehl]

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:

Crosstable numeric values

[Anonymous]

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

[ashfaq_haseeb]

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

[alexandros17]

If nothing of the above works do:

myField * 1 as myField

Simply multiply by 1 ...