How to filter out data rows with specific word(s).

How to filter out data rows with specific word(s).

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 suggests an alternative way to filter such data rows. 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 ‘~=’; it makes the expression limit all the data rows that contains the word ‘test’ in data column [Comments] and not only the exact matches.