topic Re: Converting text to number in Load in QlikView

Converting text to number in Load

Fri, 20 Jun 2014 19:43:34 GMT

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(

Re: Converting text to number in Load

Sat, 21 Jun 2014 19:24:01 GMT

Hi,

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

Evaluate(Year).

Re: Converting text to number in Load

maxgro — Sat, 21 Jun 2014 21:08:22 GMT

maybe

num#(Year)

Re: Converting text to number in Load

MarcoWedel — Sun, 22 Jun 2014 22:29:24 GMT

Hi,

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

hope this helps

regards

Marco

Re: Converting text to number in Load

swuehl — Sun, 22 Jun 2014 23:34:32 GMT

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

Re: Converting text to number in Load

Anonymous — Mon, 23 Jun 2014 01:44:30 GMT

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

Re: Converting text to number in Load

ashfaq_haseeb — Mon, 23 Jun 2014 06:10:49 GMT

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

Re: Converting text to number in Load

alexandros17 — Mon, 23 Jun 2014 15:56:17 GMT

If nothing of the above works do:

myField * 1 as myField

Simply multiply by 1 ...