Spotfire: Utilizing Column Properties (and How to Make Your Own)

Did you know that you can create your own Column Property and then designate a property value for each column?  Did you know that you can do this not only in the Property Control window but also in the Column Property window for even more flexibility?

Why might you want to create your own Column Property?  Maybe you want to group a handful of columns together by some shared quality.  Why might you want to do that?  Both Custom Expressions and Search Expressions can utilize a Column Property, so you can create these properties for columns and then reference the columns with a shared quality.

Frankly, there is a lot of uncharted territory and potential innovation within this relatively unknown Spotfire feature, so our main focus today will be to discuss briefly what Column Properties are, demonstrate two ways to make your own, and show one possible use case.

First, a review on Column Properties.  If you are comfortable with them, feel free to skip down to the How to Make a Custom Column Property section.

What are Column Properties?

Column Properties are simply metadata about each column, which we conveniently can use to reference said columns.  For example, we can reference a column’s name as a property rather than calling it by name in an expression in order to ensure we call the column correctly after a name change.  Typing [Column 1] in an expression only works as long as it is named “Column 1” whereas utilizing the Column Property by typing ${TableName}.{Column 1}.{Name} will dynamically call the name column even after a name change. A name change might occur because of a manual change or replaced data.

We can see what properties a column has in both the Column Properties and Calculated Column / Custom Expression windows.  All of the items in the header of the Column Properties window are column properties.

We can see what property value a column has in a Calculated Column / Custom Expression window and also call it there.

Above, we have highlighted the Project column in the Available columns window and can see the corresponding property Name and Value in the Available properties for column window.  We can call all of these properties by clicking the Insert Properties button.  The purple text (purple indicating dynamic) in the Expression window results from doing so with Project selected in the Available columns window.  As we see in the Resulting expression window, this translates simply to “Project”.

If we replace the data and match the Project column with a differently named column, this expression would change and would then read ${Source Data}.{NewColumnName}.{Name}.  The Resulting expression window would then register that new name.

For more on Column Properties, check out this previous article.

How to Make a Custom Column Property

Now that we’ve reviewed Column Properties and how to use them, let’s look at how we can make our own.

Property Control Window – Simple Boolean Expressions

We can make the simplest form of Column Property in the Property Control window (in a text area, right click > Edit HTML > third icon from the right in the toolbar).  Whichever selection you choose will allow you to create a new Column Property.  Go to the Column Properties tab and then click New.

After clicking New, we will get this window, where we can initialize the Column Property parameters.

                 

In this window, we will name the property and give it a default value.  Spotfire will assign the default value to ALL columns.  Also, note that you can dictate what data type you want the property’s values to be.

The Property Control window provides another option when we choose a drop-down window or list box as the Property Control type via the Select Columns button.

 

The Select Columns window allows us to create a new Column Property using the New button much as before; however, notably, now only a Boolean (true/false) data type is possible.  What this new Column Property does is say whether or not a column has this property, or is part of this group.  The column either is (true) or isn’t (false). When we hit OK, the Property Control window automatically creates an expression that limits the Selectable Columns to those that are true for said Column Property.  The user will only be able to choose from these columns when selecting the Document Property value in the list box or drop-down menu.

Above, we see the expression Example Property:true limits the columns in the list that appears below it.  That Column Property results in a Drop-down menu which only has those columns as a selectable value for the Document Property.

Notably, we could create another Column Property and use it in conjunction with the first by using “or”:  Example Property:true or Example Property:true.  Multiple Column Properties allow different combinations for different Property Controls and provide a great deal of flexibility.  For more on the Search Expression language, check out this blog post.

Still, there is a way to have even more flexibility in the creation of our Column Properties.

Column Property Window – More Expression Options

If we go to Edit > Column Properties and click on the Properties tab, we then get a button to make a New property once again.  This method also allows for all data types.  Again, we set a default value for the property.  However, when we select a column up top, then scroll down in the Available properties window to the new property, we can now edit each column’s property to whatever value we want (within the data type).

As you can see below, we have created a CategoricalColumnProp with three groups–Default Group, Secondary Group, and Tertiary Group.

We can now use the multiple groups in our search expression like we did before calling multiple Column Properties:

This example shows how to utilize custom Column Properties in a Search Expression window in order to limit what columns the expression can search.

While we’re not sure of the actual use case, you can also call a Column Property in a Calculated Column / Custom Expression window (for a use case involving applying the Name property in a Custom Expression, see the previous blog post we linked to).  As stated, this Spotfire topic remains rather underdeveloped.  However, we also love that we have this kind of flexibility and these kinds of tools at our disposal.

Have you utilized a custom Column Property in an interesting way?  If so, we’d love to hear about it in the comments.

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 *