topic Re: Converting text to number in QlikView

Converting text to number

Wed, 25 Jun 2014 18:29:00 GMT

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);

Re: Converting text to number

maxgro — Thu, 26 Jun 2014 16:28:55 GMT

if possible, post the qvw or the qvd

Re: Converting text to number

vgutkovsky — Thu, 26 Jun 2014 16:29:03 GMT

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

Re: Converting text to number

ashfaq_haseeb — Thu, 26 Jun 2014 16:32:11 GMT

Hi Try below

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

Regards

ASHFAQ

Re: Converting text to number

Fri, 27 Jun 2014 15:16:22 GMT

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

Re: Converting text to number

vgutkovsky — Fri, 27 Jun 2014 15:19:09 GMT

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

Regards,

Vlad