- Have you experienced issues joining with calculated columns?
- Have you experienced issues getting calculated columns to work with pivot and unpivot?
- Have you ever wondered why insert, transformations, calculated column is even an option?
For this week’s post, I want to discuss calculated columns. There are two ways to insert calculated columns into a data table:
- Insert, Calculated Column
- Insert, Transformation, Calculate new column.
There is a key difference between the two, and that is when they can be updated. Transformation steps are performed only when an analysis is first opened and when data sources are refreshed. This makes them more “stable”, for lack of a better word, than creating calculations via Insert, Calculated Column. When you insert a calculated column, it can theoretically change at any point in time, especially if property controls are incorporated. For example, if you used a calculated column that included an input property control to join with, anytime the input changed, the table would need to be reconstructed. Therefore, calculated columns cannot be used with joins or with pivot/unpivot. They will simply not be presented as an option to work with.
The screenshots below show a data set with a column called BOE that is a calculated column. You can also see the attempts at pivoting and inserting columns. BOE (calc) does not show up as an option.
Thus, if you want to join on a calculated column or use a calculated column in a pivot/upivot (even if it’s just to pass through), you must create those calculations as transformations.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!