Keeping Spotfire Visualizations Working when Replacing Data by Using Column Properties

Do you ever try to replace data in a Spotfire dxp only to find many of the visualizations no longer work?

At Ruths.ai, we create Spotfire templates as one of our main products.  Often, people have to replace our source data with their own to utilize our templates.  However, this can cause some complications when they match columns with different names than the ones in our source data.  Ideally, people would like to keep their column names because the names have business implications. Yet, when that column name has been hard coded into a Spotfire expression, a visualization, calculated column, or data limiting expression could break.

Until now.

In this post, we will demonstrate how to use column properties to ensure that expressions will remain intact in a Spotfire dxp even after changing a column name when replacing data.    

In a recent template, I utilized a data limiting expression that limited the data to only projects and scenarios that matched a user inputted list.  Take a look at the expression:

 

This expression says that if the Project column matches one of the names listed in the ProjectCol document property, we will take the values when the Scenario column equals the name represented by the Scenario1 document property.  But, don’t sweat the details of the expression, which gives us the following:

We see along the y axis only chosen projects and in the title that only the chose Scenario 2 appears in the bar chart.

But, let’s turn our attention to the [Project] and [Scenario] column calls back in the expression.  These columns are hard coded.  If we replace the data and those columns with ones with different names, we get this:

     

Now in the expression, [Project] and [Scenario] are red instead of green and, of course, the visualization can’t find the referenced column.

So, what are we to do?  Instead of calling the column by  a static name, we need to call the name, whatever it is, of the column which occupies that column index.  That way, the expression will call whatever column might replace the column in question by referencing its new name.

Let’s go back and look at our Expression window  before I changed the data.

When I have Project selected as my available column, we see in the properties window on the right, there is a Name property (property type:  column) and its Value is Project.  Likewise, here is the view if I have Scenario selected:

Now, the Value is Scenario.  Each column has several properties and when inserted into an expression, the Value of whatever column is selected will be called.  Inserting the above property results in ${Source Data}.{Scenario}.{Name}.  We are calling the name of the column in that Data table.  The beauty of the column properties is that they are dynamic:  they change when data is replaced, so if we change the name of a column, the column property for name will change and we can call the new name with this knowledge.

Of course, we can look at the Column Properties outside of the expression window by going to Edit > Column Properties.

Above, we see the Column Properties after I replaced the data with newly named data:  Project and Scenario are now ProjectA and ScenarioA.

So, let’s put this information together to create a dynamic expression using the Column Property, Name.  Before, our expression was …

 …and we will change it to… .

The purple coloring indicated dynamic changes.  When we look at the Resulting Expression window, a handy tool for seeing expressions through the eyes of Spotfire, we see:

Again, don’t worry about the whole expression, just notice that the opening calls ProjectA rather than Project.  Our column name in the expression has dynamically changed because we called the Column Property for Name rather than the hard coded Name itself.

Even better, we can use this trick in Calculated Columns and Custom Expressions in addition to a Data Limiting expression.

No more worries about breaking visualizations when you replace the data with differently named columns!

Jason is a Junior 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 Hidden Markov Models. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.

Leave a Comment

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