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

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

book

Article ID: KB0071449

calendar_today

Updated On:

Products

Spotfire Analyst

Description

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.

Issue/Introduction

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

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).
    Screenshot of partial search matching implementation in a Text Area
    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])

    Additional Information

    Doc: TIBCO Spotfire Analyst Help > Text Functions: Doc: TIBCO Spotfire Analyst Help > How to use Text Area Doc: TIBCO Spotfire Analyst Help > Details on Property Control

    Attachments

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