Subtracting between two String columns that contain numeric-convertible elements

Subtracting between two String columns that contain numeric-convertible elements

In a Spotfire calculated column or custom expression, the Real() function can be used to enable subtraction between two String columns that contain numeric-convertible elements.
  Spotfire data tables are organized by columns, not by cells. In the same way that a column in a relational database (such as Oracle or Microsoft SQL Server) can only be of one data type, each column in a Spotfire data table can only be of one data type (such as Integer, Real or String).

The presence of just one character element (such as "abc" or "z1001") in a column that is otherwise numeric (in a column of Microsoft Excel cells, for example) is enough to make Spotfire's default import process convert that column into a String column.

Arithmetic is not meaningful for character strings, so arithmetic functions are not implemented for columns whose data type is String.

One way to get around this is to wrap each such column in a call to Real() before using it to do arithmetic in an expression, as in the following example:

   Real([FirstColumn]) - Real([SecondColumn])

Elements in either column that cannot be expressed as real numbers will be treated as missing values (NULLs), which will lead to NULL values for the corresponding elements in the calculated column. These elements will display as empty cells.


This is illustrated in the attached "ExampleTable.dxp" file.


The following is the "ExampleTable" data table from that file:

___________________________________________________
ExampleTable

FirstColumn    SecondColumn    CalculatedColumn
17             10                       7.00000
3.14159        14                     -10.85841
House          68
-42            Airplane
Car            Boat
98.6           73                      25.60000
Bicycle        29
___________________________________________________