Product:TIBCO Spotfire Analyst
Versions:All
Summary:
This article describes one way to filter out data rows with specific word(s) in TIBCO Spotfire client.
Details:
In some cases, it might be required to filter out data rows from a data table based on the presence of certain word(s). Words or data strings like ‘Test data’, ‘Dummy data’, ‘Confidential’, ‘incorrect’ etc. are indicative that the data row might contain misleading, secret or wrong information and it should not be considered for analysis.
If the data column in question has a limited and unique list of values, then such misleading data rows can be easily filtered with the help of built-in Spotfire filters. However, there can be scenarios where such words appear in a natural language data column (e.g. under ‘comments’ or ‘description’ columns) and cannot be filtered directly. The below resolution, which uses the "Limit data using expression" functionality to limit the data used in the plot, provides an alternative way to filter such data rows.
Resolution:
Spotfire provides a powerful way to limit data by writing a custom expression. Below are the steps to write such custom expression:
1. Right click on the visualization.
2. Click on ‘Properties’
3. In the ‘Properties’ dialog box, click on ‘Data’ >> ‘Limit data using expression’ >> ‘Edit…’.
To achieve our requirement, the below data limiting custom expression can be used:
not(Lower([Comments]) ~= 'test')
Here, [Comments] is the name of the data column and ‘test’ is the specific word we are looking for. This expression will limit the rows based on the occurrence of word ‘test’. Notice the operator ‘~=’ which uses regular expressions, giving you a very powerful tool for filtering your data in just the right way - in this simple example, it makes the expression limit all the data rows that contains the word ‘test’ in data column [Comments] and not only the exact matches. In this example, the "Lower" function was also used to get a lowercase version of [Comments], so there was no need to take varying case into account in the comparison.
Versions:All
Summary:
This article describes one way to filter out data rows with specific word(s) in TIBCO Spotfire client.
Details:
In some cases, it might be required to filter out data rows from a data table based on the presence of certain word(s). Words or data strings like ‘Test data’, ‘Dummy data’, ‘Confidential’, ‘incorrect’ etc. are indicative that the data row might contain misleading, secret or wrong information and it should not be considered for analysis.
If the data column in question has a limited and unique list of values, then such misleading data rows can be easily filtered with the help of built-in Spotfire filters. However, there can be scenarios where such words appear in a natural language data column (e.g. under ‘comments’ or ‘description’ columns) and cannot be filtered directly. The below resolution, which uses the "Limit data using expression" functionality to limit the data used in the plot, provides an alternative way to filter such data rows.
Resolution:
Spotfire provides a powerful way to limit data by writing a custom expression. Below are the steps to write such custom expression:
1. Right click on the visualization.
2. Click on ‘Properties’
3. In the ‘Properties’ dialog box, click on ‘Data’ >> ‘Limit data using expression’ >> ‘Edit…’.
To achieve our requirement, the below data limiting custom expression can be used:
not(Lower([Comments]) ~= 'test')
Here, [Comments] is the name of the data column and ‘test’ is the specific word we are looking for. This expression will limit the rows based on the occurrence of word ‘test’. Notice the operator ‘~=’ which uses regular expressions, giving you a very powerful tool for filtering your data in just the right way - in this simple example, it makes the expression limit all the data rows that contains the word ‘test’ in data column [Comments] and not only the exact matches. In this example, the "Lower" function was also used to get a lowercase version of [Comments], so there was no need to take varying case into account in the comparison.