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

book

Article ID: KB0080409

calendar_today

Updated On:

Products	Versions
Spotfire Analyst	All Versions

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.

Issue/Introduction

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

Resolution

Option 1 (Recommended):

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 now be visible, which means that it is available to use as a match column in a join.