Transforming Data

Sometimes the data you want to analyze in Spotfire is not in the most appropriate format, or may contain errors. It can therefore be useful to perform transformations on the data in order to get the best results from the analysis.

There are several methods that can be used to transform your data:

Calculate and replace column allows you to replace a column in the data table with a calculated column.
Calculate new column allows you to add a calculated column to the data table.
Change column names allows you to change the name of one or more of the columns in the data table.
Change data types allows you to change the data type for one or more of the columns in the data table.
Data function allows you to use a previously registered data function as a transformation step.
Exclude columns allows you to exclude one or more of the columns from the data table.
Normalization allows you to normalize the data prior to addition of the data table.
Pivot allows you to pivot the data – to change the data table from a tall/skinny format to a short/wide format.
Unpivot allows you to unpivot the data – to change the data table from a short/wide format to a tall/skinny format.
Additional transformations may be available to you if these have been added locally.

Transformations can be applied either when data is loaded, or later on, when the data has already been loaded into Spotfire. You can perform transformations on most of the "regular" column types that are loaded into Spotfire, but not on certain column types whose content changes depending on selections you make in the analysis. Calculated columns, columns that were created by adding tags to the analysis, and columns created by using tools like K-means Clustering and Line Similarity are some examples of column types that you cannot apply transformations to. Columns that cannot be transformed will not be available for selection in any of the settings dialogs used for transformations. Also, data that is located in an external data source (in-database data) cannot be transformed. However, if you add data from an external data source, and select to import the data table into Spotfire, you can apply transformations to the data after it has been loaded, by using Insert Transformations, as described below.

To apply transformations when loading data:

Select File > Add Data Tables....
Click Add and select the type of data to add from the drop-down list.
Specify which file, information link, etc., to add.

Comment: You can also apply transformations when you replace a data table and when you insert columns or rows to an already existing data table.

Click Show transformations to expand the lower part of the dialog, where the settings for adding transformations are located.
In the Transformations drop-down list, select the type of transformation you want to perform on the data.
Click Add... to open a dialog with settings relevant for the selected transformation type. When you have specified the necessary settings, click OK to return to the Add Data Tables dialog.
Repeat steps 5 and 6 to add more transformations.

Tip: Click on the Preview button to see what the result will be with the added transformations.

Click OK in the Add Data Tables dialog to load the data with the added transformations to the analysis.

To apply transformations to data already loaded into the analysis:

Select Insert > Transformations....
Select the data table you want to add transformations to.
In the Transformations drop-down list, select the type of transformation you want to perform on the data.
Click Add... to open a dialog with settings relevant for the selected transformation type. When you have specified the necessary settings, click OK to return to the Insert Transformations dialog.
Repeat steps 3 and 4 to add more transformations.

Tip: Click on the Preview button to see what the result will be with the added transformations.

Click OK to apply the selected transformations to the data.