Product: TIBCO Spotfire®
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
___________________________________________________