0

I have a string field with mostly numeric values like 13.4, but some have 13.4%. I am trying to use the following expression to remove the % symbols and retain just the numeric values to convert the field to integer.

Here is what I have so far in the expression definition of Cognos 8 Report Studio:

IF(POSITION('%' IN [FIELD1]) = NULL) THEN 
  /*** this captures rows with valid data **/
  ([FIELD1]) 
ELSE 
  /** trying to remove the % sign from rows with data like this 13.4% **/
  (SUBSTRING([FIELD1]), 1, POSITION('%' IN [FIELD1])))

Any hints/help is much appreciated.

Marcus Rickert
  • 4,138
  • 3
  • 24
  • 29
user2525624
  • 15
  • 1
  • 3

2 Answers2

1

An easy way to do this is to use the trim() function. The following will remove any trailing % characters:

TRIM(trailing '%',[FIELD1])

Johnsonium
  • 2,005
  • 1
  • 13
  • 15
0

The approach you are using is feasable. However, the syntax you are using is not compatible with the version of the ReportStudio that I'm familiar with. Below you will find an updated expression which works for me.

IF ( POSITION( '%'; [FIELD1]) = 0)  THEN 
    ( [FIELD1] ) 
ELSE 
    ( SUBSTRING( [FIELD1]; 1; POSITION( '%'; [FIELD1]) - 1 ) )

Since character positions in strings are 1-based in Cognos it's important to substract 1 from the position returned by POSITION(). Otherwise you would only cut off characters after the percent sign.

Another note: what you are doing here is data cleansing. It's usually more advantageous to push these chores down to a lower level of the data retrieval chain, e.g. the Data Warehouse or at least the Framework Manager model, so that at the reporting level you can use this field as numeric field directly.

Marcus Rickert
  • 4,138
  • 3
  • 24
  • 29
  • Thank you Marcus, this worked. I agree with your comment about it being a data cleansing chore; something I'll have to tackle another day. I appreciate the quick response and simplicity of your resolution. – user2525624 Oct 23 '14 at 14:42