How to use a calculated column when matching columns for joins within an analysis.

How to use a calculated column when matching columns for joins within an analysis.

Environment: Product: TIBCO Spotfire (desktop client / Analyst / Professional)

Version: 6.5 and later

OS: All Supported Operating Systems
--------------------
Description: If you create a Calculated Column via "Insert > Calculated Column", you will not be able to use that as a match column in subsequent joins, as when inserting additional columns via "Insert > Columns" or when defining column matches via "Edit > Data Table Properties > Column Matches". There are two methods for using Calculated Columns as the match column when performing joins in an analysis.

Resolution: Option 1 (Preferred):

Instead of inserting your calculated column via "Insert > Calculated Column" you should instead insert a data table transformation via "Insert > Transformations". To do this:

1. Select "Insert > Transformations".

2. Select your data table in the data table selection drop down.


3. Select "Transformations: Calculated new column".

4. Click "Add".

5. In the "Calculate New Column" dialog, enter your column expression as usual.

6. Click OK.

Option 2:

If you do insert a calculated column via "Insert > Calculated Column", you can still use that as a join match column if you first freeze the column. To do this:

1. Create your calculated column via "Insert > Calculated Column".

2. Select "Edit > Column Properties".

3. Select your new calculated column.

4. Click the "Freeze Column" button.

Note: Option 2 is not normally recommended due to the complications of working with frozen columns. Freezing a column will force the data table to be embedded. This restriction on the data table will remain even if you remove the frozen columns from the data table at a later stage. It is not possible to unlock a previously frozen column.

When inserting columns and when defining "Column Matches", the calculated column will be visible, which means that it is available to use as a match column in a join.

Reference: TIBCO Spotfire Web Help

• Column Matching

o https://docs.tibco.com/pub/sfire-analyst/7.5.0/doc/html/WebHelp/md/md_column_matching.htm

• How to Insert Columns

o https://docs.tibco.com/pub/sfire-analyst/7.5.0/doc/html/WebHelp/add/add_how_to_insert_columns.htm

• Details on Column Properties – General > Freeze Columns

o https://docs.tibco.com/pub/sfire-analyst/7.5.0/doc/html/WebHelp/data/data_details_on_column_properties_general.htm

Keywords: calculated column, column match, join, column matches, transformation, calculated new column, match, freeze, frozen, embedded, unfreeze