Details on New/Edit Match

When you combine data from different data tables in one visualization, Spotfire will automatically match columns that have the same name and data type. When the column names in the two data tables are not the same, or, when the grouping of the values in the columns to match differ, you can use the dialog below to add or edit matches manually.

There are also cases when you may need to do an additional transformation on the values in the two columns. For example, if you have two columns that should be matched but they differ in case, then you want the column match to just use the columns whereas the transformation could apply the Upper method call on both sides. Transformations could also be used to match an integer with a string. Basically, you can use any row methods in the in-memory data engine as transformations.

Transformations are also useful for in-database cases where you do not have any row methods in the systems (i.e., when working with cubes) but you need to transform data before the it can be joined.

To reach the New/Edit Match dialog:

Select Edit > Data Table Properties.
In the Data tables list, select the data table containing the column you want to match with a column in another data table.
Click on the Column Matches tab.
To add a new match, click on the New... button. To edit an existing match, select the match you want to edit in the Current matches for the selected data table list, then click in the Edit... button.

Option	Description
Left data table	The data table containing the column you want to match with a column in another data table. If this dialog was opened from the Data Table Properties dialog, the data table that is selected in the Data Table Properties dialog will be shown here. If you reached this dialog by clicking Add match on the Data page of Visualization Properties, the data table that is the main data table in the visualization will be shown here.
Right data table	Lists all data tables currently available in the analysis. Select the data table containing the column you want to match with the column in the Left data table. If you reached this dialog by clicking Add match on the Data page of Visualization Properties, the data table will be preselected here.
Left matching column	Lists all columns available in the Left data table. Select the column to be used in the match. When a column has been selected, the Methods submenu is made available. If desired, you can choose a method that modifies the content of the selected left column according to the method. 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.
Right matching column	Lists all columns available in the Right data table. Select the column to be used in the match. When a column has been selected, the Methods submenu is made available. If desired, you can choose a method that modifies the content of the selected right column according to the method. 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.
Sample value	Displays the resulting first value of the selected left and right matching columns, after any methods have been applied.
Left column value transformation	Allows you to add a transformation on the value from the left matching column. You can either click on the arrow and select one of the available predefined methods or right-click on the selector and type your own Custom Expression. This is where you apply changes to make sure that the category names match between the two tables. For example, for a string column it is possible to use the methods "Lower" or "Upper" to convert strings to lowercase or uppercase, respectively.
Right column value transformation	Allows you to add a transformation on the value from the right matching column. You can either click on the arrow and select one of the available predefined methods or right-click on the selector and type your own Custom Expression. This is where you apply changes to make sure that the category names match between the two tables. For example, for a string column it is possible to use the methods "Lower" or "Upper" to convert strings to lowercase or uppercase, respectively.
Sample value	Displays the resulting first value of the selected left and right columns after any transforms have been applied.
Preview	Shows a preview of the first 100 distinct values in an alphabetically sorted order, after all methods and transformations set in the dialog have been applied. This preview may 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 may 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.