Using Calculated Columns in Spotfire in Other Transformations

Have you ever created a Spotfire Calculated Column then wondered where the heck it went when you tried to do a transformation such as a pivot/unpivot/insert row/insert column?  Or have you ever wanted to use a calculated column as a key column when tagging or manually changing data values but don’t see it anywhere?  This can be extremely frustrating until you realize how easy the solution is.  All you have to do is create the calculated column via a transformation rather from the Column Properties or Add Calculated Columns windows.

To do so, go to Data > Transform data (or Insert > Transformations pre Spotfire X) and select Calculate new column then Add.  From there, the expression window should be familiar and you can create your expression the same way.  However, when you go to Column Properties, you will see the Column Type is now Imported rather than Calculated.  The fundamental data table has been altered, which allows us to use it in other transformations.

Confused?  Don’t be, just hold onto the idea that you have to calculate a column via transformation to use it in other transformations.

If you are interested a little more in why, follow us to after the jump.

In addition to the way shown above, you can also create a calculated column via a transformation using the Data canvas window.  The window will look something like below, depending on how many data table manipulations you have performed.

You can click any of the above tables and will get information about the evolution of that table as seen below:

From there, you can click the plus symbol to add a Calculated Column as a transformation there.

But, I just gave you another how, not the why I promised you.

If you look at the first diagram, you see the production table is the last state in a series of states.  If I create a Calculated Column via traditional methods like Column Properties or Add calculated column, I am simply adding the column to the end of the line. Since the Calculated Column in this scenario is not a transformation, other transformation occur before it (like the joining of the Well Header above).  In short, Spotfire WANTS all of the transformations to happen before the Calculated Column in case the transformations are needed for the column.

However, when you calculate a column via a transformation, the column is applied at whatever point you inserted it into, in whatever state the data is currently in.  We can then apply that column to any subsequent transformation because it has now happened at an earlier state.

Make sense?  I hope so.  If not, just remember to create your calculate column as a transformation to use it in another transformation!

Jason is a Data Scientist at Ruths.ai with a master’s degree in Predictive Analytics and Data Science from Northwestern University.  He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Support Vector Machines.  With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.

Leave a Reply

Your email address will not be published. Required fields are marked *