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.

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