Remove % sign

[rittermd]

I have an excel spreadsheet where the measures show for example 10.99% in the cells.

I think that I need to get rid of the % sign in order to do calculations with these cell values but I am not sure of the best way to do this.

This is what I am coding in the script and it is not working correctly because the values in the cells are not numeric:

The entire process so far is this:

Load excel sheet:

[ALL$]:

LOAD [Branch],

  [2016],

  [2015],

  [2014],

  [2013],

  [2012],

  [2011],

  [2010]

FROM [lib://AttachedFiles/Branch CM Modified.xls]

(biff, embedded labels, table is ALL$);

Then I do a crosstable:

Years:

Crosstable (Year, measure, 1)

  LOAD *

    Resident [ALL$];

Drop Table [ALL$];

Then I am attempting to do the Rank:

Rankings:

Load *,

If (measure<'0','Negative',

   If (measure >='0' and measure <'5','0%<5%',

     If (measure >='5' and measure <'10','5%<10%')))

  as Rank

  Resident Years;

[Anonymous · Accepted solution]

Instead of 5% and 10% can you try 0.05 and 0.1. Qlikview converts the % automatically into decimals.

[sunny_talwar]

How about this

Rankings:

Load *,

If(Num#(measure, '##.##%') < 0,'Negative',

   If(Num#(measure, '##.##%')< 0.05,'0%<5%',

     If(Num#(measure, '##.##%')<0.10, '5%<10%'))) as Rank

Resident Years;