Details on Calculate and Replace Column

This dialog is used to calculate a new column and replace a column in a data table with the new, calculated column.

Tip: You can replace a data table with a transformed version of itself.

To add a Calculate and Replace Column transformation when adding a data table:

Select File > Add Data Tables... and add the data of interest.
Click Show transformations.
Select Calculate and replace column from the drop-down list and click Add....

Tip: The Show transformations-section is also available when replacing data tables and when adding columns or rows to a data table.

To add a Calculate and Replace Column transformation to data that is already loaded into Spotfire:

Select Insert > Transformations....
In the Data table drop-down list, select the data table you want to add the transformation to.
Select Calculate and replace column from the drop-down list and click Add....

Option	Description
Column to replace	Lists all available columns in the selected data table. Select the column you want to replace by clicking on it.
Available columns	Shows all available columns. Select a column by clicking on it in the list and then click on the Insert Columns button, or double-click on the column to send it to the Expression field. You can narrow down the list of available columns by typing a part of a name in the "Type to search" field. You can also enter an expression in the field, using the rules described on the Searching in TIBCO Spotfire page. Press Ctrl or Shift to select multiple columns.
Insert Columns	Inserts the selected columns in the Expression field.
Available properties for column	Shows all properties that you can use in the calculation of a new column. The column properties are specific to the column selected in the Available columns list to the left. Select a property by clicking on it in the list and then click on the Insert Properties button, or double-click on the property to send it to the Expression field. The property will automatically be inserted as a value in this context. See Properties in Expressions for more information. You can narrow down the list of available properties by using the search field.
Insert Properties	Inserts the properties selected in the Available properties for column list at the current cursor position in the Expression field.
Category	Select a category of functions to limit the choices in the Function list: All functions Binning functions Conversion functions Date and Time functions Logical functions Math functions Operators Property functions Ranking functions Spatial functions Statistical functions Text functions
Function	Select a function by clicking on it in the list and then click on the Insert Function button, or double-click on the function to send it to the Expression field. You can narrow down the list by typing a part of a name in the field where it says "Type to search".
Description	Shows a brief description of the selected function. For more detailed descriptions, see the Functions chapter.
Insert Function	Inserts the selected function in the Expression field.
Expression	This is the text field in which you build your expression. You can insert columns and functions from the lists or enter text as in any standard text editor. See Expression and Script Editor Keyboard Shortcuts for information about available shortcuts.
Recent expressions	Displays the ten expressions you have most recently created. You can select one of these and click the Insert button to insert the expression into the Expression field.
Insert	Inserts the selected Recent expression into the Expression field. This will replace the entire content of the Expression field.
Resulting expression	Of interest when preprocessor functions (such as ${PropertyName}) are used in the expression only. Displays the expression after all occurrences of the property have been replaced with its current value or values.
Column name	Type a name for the calculated column.
Sample result	Displays the result of applying the current expression to the first row of the data table. Note that if aggregating functions (such as, "Count") are used in the expression, only the first 100 rows will be used in the sample calculation. If this field shows an error there is a problem with the expression. Moving the mouse pointer over the red exclamation mark next to the Expression field will display an explanation of what is wrong.
Type	Shows the data type of the calculated column.
Formatting...	Opens the Formatting dialog, where you can change the formatting of the calculated column.