Adding column matches manually

If you want to show data from multiple data tables in a single visualization, the data tables must have at least one column match available. If the data in the data tables is similar, column matching often happens automatically, but you can add manual matches using the installed client when needed.

About this task

Read more in Multiple data tables in one visualization and Column matches.

Tip: Another way to fix some issues with missing column matches is to rename the columns to group by in the additional data tables, so they use identical names (and data types) as in the main data table. Then, the automatic column matching will kick in.

Before you begin

Manual column matches must be authored in the installed client.

Procedure

In the installed Spotfire client, open the analysis of interest or load data tables into a new analysis.
On the menu bar, select Data > Column matches.
In the Data tables list, select the data table to use as the main data table in the visualization.
Click New, to the right of the list showing the Current matches for the selected data table.
The New Match dialog is opened.
In the Right data table drop-down list, select the data table to add a match for.
The data table in the Left data table drop-down list is already set to the data table that was selected in the Column matches dialog.
In the Left matching column selector, choose the column to use for the match from the left data table.

Note: If needed, click the arrow on the column selector and the arrow on Methods in the drop-down list and choose a method to apply to the column to get a better match. For example, you can change the data type for the column.

What methods are available depends on the data type of the column, and, if you are using in-database data, on the methods available for the data connector. You can also right-click to open the Custom Expression dialog.
This is where you should apply any methods that affect the grouping of your values. For example, if you have DateTime values but you want the match to be done on the Year only.
In the Right matching column selector, choose the column to use for the match from the right data table.

Note: If needed, you can add a method to the column. See step 6.
If needed, you can also add a Left column value transformation and/or a Right column value transformation by clicking the arrow on Value and selecting something under Methods.
In this step, you can transform the values within the column to get a better match. For example, you can use the "Upper" method to convert all values in a column to use uppercase letters, if the casing is different in the two columns and you therefore not get any matches. You can also use a custom expression. The available methods differ with the data type of the selected column. See Creating matches to ensure same grouping of values for an example.

Transformations are also useful for in-database cases where you do not have any row methods in the systems (that is, when working with cubes) but you need to transform data before the it can be joined.
Optionally, click Preview to see a preview of the first 100 distinct values in an alphabetically sorted order, after all methods and transformations defined in the dialog have been applied.

This preview might help you discover cases where the matching fails.

When you are working with external data, no automatic samples can be shown, because any previews will send queries to the external system and might cause unnecessary stress on the data source. By clicking Preview only when you think you are done with the matching you can help reducing the load on your data source.
Click OK to close the dialog.
Repeat steps 4-9 if more column matches are needed.
Click OK to close the Column Matches dialog.

Results

The column matches are added to the selected data tables.