How to Create a Property Control Filter with Partial String Matching in TIBCO Spotfire

Product:TIBCO Spotfire Analyst
Versions:All

Summary:
How to create a filter with partial string matching with property controls and calculated columns in TIBCO Spotfire.

Details:
By default, the search in List Box and Text filters matches on the string from the beginning of each word only (searching for "an" will match "anvil" but not "banana"). It is possible to create a partial word match which will match your search string anywhere in the values by using using property controls and calculated columns.

Resolution:
Assume a data table containing the countries of the world like:

#    Country    Population
1    Afghanistan    34169169
2    Albania    2911428
3    Algeria    41063753
...

Follow these steps:

1. Go to 'Insert' > 'Text Area' to create a new Text Area
2. Right click on the next Text Area, and select 'Edit Text Area'
3. Go to 'Insert Property Control' > 'Input Field' to create a text 'Input Field' property control. Click 'New' to create a new property called 'searchString' of data type 'String' for the property control.
4. Go to 'Data' > ' 'Add Calculated column...' (or 'Insert' > 'Calculated Column' if using an older version of Spotfire) to create a calculated column called 'Filtered Country' with expression:
   • If("${searchString}" != "" and [Country]~="${searchString}",[Country])
5. In the Text Area, go to 'Insert Property Control' and create a 'List Box' or 'List Box (Multi select)' property control with:
   • Click 'New' to create a new property called 'selectedCountries' of data type 'String' for the property control (if a multi-select list box is used, this will result in a 'String List' data type)
   • Set property values in column: Unique values in column
   • Column: Filtered Country

See the attached 'Partial Search Match Example.dxp' for an example.

You can now search for "am" and it will match "American Samoa", "Bahamas", "Cambodia", etc. ("am" anywhere in the Country string) instead of just "American Samoa" and "United States of America" ("am" in the beginning of a word).

Note: if you want to apply this filtering to a visualization, one way could be to go to the Visualization properties > 'Data' > 'Limit data using filterings', and an expression like:
[Country]=DocumentProperty("selectedCountries")
 

Option 1:

If you want all values to be displayed when no search filter is entered, remove this from the expression:

• "${searchString}" != "" and

Resulting in:

• If([Country]~="${searchString}",[Country])

Option 2:
If you want to search for your string only in the beginning of the column values (i.e to only find values that start with the search string), add a "^" in the expression resulting in:

• If("${searchString}" != "" and [Country]~="^${searchString}",[Country])