Converting text to number

[#1]

I have a field being read in a crosstable as text.  However, I need it to be converted to a number so that I may perform mathematical functions on it.  I have my code below.  Unfortuantely, it still sees the field as text.  The field is TotalParticipants which becomes NumberOfTotalParticipants.  Both look like numbers, but they are text (I know this from when I choose how to line up data types in my table...text to the left and numbers to the right.  Even after applying the Num# function they still line up as if they were text AND I cannot perform any mathematical functions on it).

LOAD [ ] as RunnerType,

     [ 1] as RunningFrequency,

     SubField([Total Participants ('11 - '12 +/-)], '(', 1) as TotalParticipants

     //SubField([Total Participants ('11 - '12 +/-)], ')', 1) as PercentChange

FROM

[http://www.runningusa.org/state-of-sport-2013-part-II?returnTo=annual-reports]

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 4))

));

STORE [Temp U.S. Running Participation Numbers 2012] into Temp_US_Running_Participation_Numbers_2012.qvd(qvd);

DROP TABLE [Temp U.S. Running Participation Numbers 2012];



[Final U.S. Running Participation Numbers 2012]:

LOAD RunnerType,

     RunningFrequency,

     TotalParticipants,

     num#(TotalParticipants, '#') as NumberOfTotalParticipants

     //PercentChange

FROM



(qvd);

[maxgro]

if possible, post the qvw or the qvd

[vgutkovsky]

Well, that's because your data is coming through a bit funny. Here's an example of one of your field values: 51,450,000 (+2.8%)

I would write the following code to deal with the above format:

num#(left([Total Participants ('11 - '12 +/-)],index([Total Participants ('11 - '12 +/-)],' ')-1),'#,##0') as TotalParticipants

Is that what you were looking for?

Regards,

Vlad

[ashfaq_haseeb]

Hi Try below

num#(TotalParticipants, '#,###,###,#00')as NumberOfTotalParticipants

Regards

ASHFAQ

[#5]

I did manage to get the data to come through with just the number. So that is all I am trying to change.

Alex

Sent from my iPhone

[vgutkovsky]

Alexander, just use the line of code I pasted above. You don't need subfield anymore.

Regards,

Vlad