Spotfire Property Controls like drop-down menus and list boxes can become cumbersome when a dataset has numerous variables and/or multiple data types. What if we don’t want all columns to be available to the user for industry reasons (some columns wouldn’t make sense) or simply efficiency reasons (too many columns to sift through)? What if only certain data types would be appropriate to use? Fortunately, Spotfire lets us limit these list Property Controls via Search Expression.
Maybe you’ve seen those little search boxes in Spotfire which allow you to search for data (columns, categories, etc). Maybe not, as I’m finding out even many Spotfire experts haven’t. Search Expressions act distinctly from Custom Expressions. These expressions do not use mathematical operations. They do not use brackets to denote a column. You cannot use another Document Property within the limiting expression. These expressions serve only to search for strings using a simplified language (usually with the goal of selecting or limiting something). Property Controls, Data Function Parameter windows, and the Information Designer are a few of the places you might find a Search Expression window and today’s discussion on Search Expressions will most likely apply to all of the scenarios.
So, let’s take a look at how we can use Search Expressions in different ways to control and limit the options in our list based Property Controls.
First, let’s revisit Property Controls vs Document Properties. What’s the difference? A Document Property is a Spotfire Value that stands alone outside a dataset, which a user can call throughout an analysis whether in an expression, data function, or elsewhere. A Property Control is a method for dictating how Document Properties are created. The distinction can seem mercurial, but when we use and manipulate Property Controls to dictate what Document Properties can be, the distinction becomes more clear.
First, let’s look at how to create a Document Property. We can use the most basic method by going to Edit > Document Properties > Properties > New. This allows us simply to create a property with a name and value, but it remains static and not dynamic. However, Property Controls allow us to create dynamic, interactive Document Properties.
Now, let’s review how to make a Control Property. For this exercise, we will make a drop-down list. To do so, we right click inside any text area and choose Edit HTML. In the toolbar, choose the third icon from the right > Drop-down list. Make a new Document Property, name it, and give it a dummy value. We will look at setting a property value via both column selection and unique values in a column. We’ll first look at column selection. This puts different columns in the drop-down as options to choose for the document property.
Now, we can dictate what values should be options for the Document Property. Below, we see the section in the Property Control window where we can enter the Search Expression and the resulting options which appear below the expression. The user will be able to select any of the columns we see out of those options from the drop-down which the Property Control will create.
So, within the Search Expression window, we can limit which columns the Control Property allows via inclusion or exclusion. The Control Property controls which values it will allow as a Document Property.
We have several different Search Expression options we can examine. For example, notice the expression above is blank and multiple data types are allowed. Let’s try to change that.
Limit by Data Type
We will start with a simple limiting expression.
In the above image, first notice that “DataType” followed by “:” calls the DataType stipulation. After that, we simply type the data type we want to allow. These types can be strings, real numbers, integers, dates, etc, and the word needs to be typed exactly as it is seen under the Data Type column in the window (ex. “Real” for real numbers).
Also notice a second important factor: we can allow multiple types by using “or”. We can also use “and”, which we will discuss later.
Limiting by Data Type can be extraordinarily useful when only certain types of data will work as an input, which honestly is most of the time. We remove both the chance of a wrong data type causing an error while also severely limiting the options to choose from.
Include or Exclude Specific Columns
We can include specific columns…
Above, we see that partial calls to a name work. The Control Property will allow anything that contains Trellis or Hierarchy within its column name.
We can also exclude specific columns using “not”:
This expression has an implied “and” between “nots” which we can add if desired for clarity. Note that before we used “or” to include and here we use “and” to exclude.
Excluding columns comes in handy when you might want to exclude a column that has already been used by another Document Property to avoid duplication.
Combine the Methods
Of course, we can combine limiting by data type and “not”.
Unique Values in Column
Up until now, we have been limiting columns which the user can select, but we can also let the user choose categories within a column and limit those. Change the “Set property value through” window to “Unique values in a column”, select the column, then use the same syntax as before. First, let’s look at the categories in the ProjectA column.
We can use the same “or” term to include only certain projects:
Search Expression Intricacies
Let’s highlight a few intricacies of these expressions, for either the column or category Property Control. First, partial terms will include anything with that partial term in it.
This column has 5 projects and the Property Control allows them all. This can cause some issues when used in conjunction with “or” statements, as we will see below…
As I start to type “Project 3”, I type “Project”, which Spotfire then reads immediately, allowing the partial term to then include ALL categories that include the term (in this case also adding the unwanted Project 4 and 5). This might not apply to your situation but can be disarming and confusing, so just remember to type out your entire statement before looking at the box below the expression to see the results.
Parenthesis and “Not”
The expressions don’t use quotations to indicate terms should be included together but parenthesis. Observe:
Whereas when we used “Project 1 or Project 2 or Project 3” the space between the project and number did not matter, here it does not read the category together but separately. We are guessing the disparity comes from something regarding the implied “and” combined with order of operations, so that the Property Control reads “not Project 2” as “not project and 2″, thereby negating everything with “Project” in the name. Since no category has “2” in it but not “Project”, we get a blank list.
In our column example, if we do a similar search for “not Value 1”, we get:
So, in this case, there is in fact a column that has “1” but not “Value” in it.
Of course, we can avoid any confusion by following the best practices to wrap any column or category name with a space in parenthesis, no matter the use case.
I hope this exploration into Search Expressions has helped clarify the topic. Not a lot of examples regarding the subject exist out there, so feel free to link to some or provide alternate usages or interpretations. Here is the TIBCO documentation on Search Expressions, which details several other keywords and their functions.
And, as always, feel free to ask questions!
Soon, I will have a sister post to this one, in which we explore how we can utilize Column Properties in the Property Control Search Expressions, so stay tuned…
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.