Filtering tables
The rows shown in a table view can be limited to just those of interest by simple or advanced filtering, defined using functionality just above the table. Saving and reusing sets of table filters is described at the end of this section.
Filtering the rows of a table interactively does not change the underlying content of the table. Some table types have a button allowing the creating of a new table containing only the visible rows.
Simple filtering
The default view of a table supports simple filtering, where rows containing a particular search term can be entered into a field to the left of the Filter button (figure 9.2). Simple filtering is enabled when there is an upwards pointing arrow at the top right of the table view. (Click on that arrow reveals advanced filtering options, described later in this section.)
Simple filtering starts automatically, as you type, unless the table has more than 10,000 rows. In that case, click on the Filter button after typing the term to filter for.
The number of rows with a match to the term is reported in the top left of the table.
The following characters have special meanings when used in the simple filtering field:
- Space Terms separated by spaces are treated as individual search terms unless the terms are placed within quotes. E.g. the term
cat dogwould return all rows with the term cat and/or the term dog in them, in any order. - Single and double quotes ' and " Enclose a term containing spaces in quotes to search for exactly that term. E.g.
"cat dog"would return rows containing the single termcat dog. - Backslash Use this term to escape special characters. For example, to search for the term term
"cat"including the quotation marks, enter\"cat\". - Minus - Please a minus symbol before a termm to exclude rows containing that term. e.g.
-cat -dogwould exclude all rows containing either cat or dog. - Colon : Specify the name of a column to be searched for the term. E.g.
Animal:catwould search for the termcatonly in a column calledAnimal. For this sort of filtering, please also refer to the advanced filtering information, below.
Figure 9.2: Filtering for rows that contain the term "neg" using the Filter button
Advanced filtering
Functionality to define sets of filter criteria is revealed by clicking on the downwards-pointing arrow at the top right of the table view, (figure 9.3).
Figure 9.3: When the Advanced filter icon is clicked on (top), Advanced filtering fields are revealed (bottom)
Each filter criterion consists of a column name, an operator and a value. Examples are described below.
Filter criteria can be added by:
- Clicking the Add (
) icon.
- Right-clicking on a value in the table and selecting the Table filters option from the menu that appears. Predefined criteria for that column and value combination will be listed (figure 9.4). Selecting one of these adds it to the list of filters at the top of the table.
Filter criteria can be removed by clicking on the (
) icons.
Figure 9.4: Right-click on a cell value and choose Table filters to reveal predefined criteria that can be added to the list of filters for this table.
Match all and Match any options allow you to specify, respectively, whether all criteria must be met for a row to shown, or whether matching a single criteria is enough for a row to be shown (figure 9.5).
The number of rows with a match to the term is reported in the top left of the table.
Figure 9.5: The same two criteria are defined, but with "Match all" selected in the top image, and "Match any" selected in the bottom image.. Six rows out of 169 match all the criteria, while 154 rows match one or both criteria.
Operators available for columns containing text are listed below. Tests for matches are not case specific.
- contains
- doesn't contain
- = Matches exactly
Does not match
- starts withStart with the term provided.
- is in list Matches at least one of the entries in the list.
- is not in list Does not match any entry in the list.
Terms in lists can be comma, semicolon, or space separated.
Operators available for columns containing numerical values are:
- <= Smaller than or equal to
- < Smaller than
- >= Greater than or equal to
- > Greater than
- abs. value < Absolute value smaller than.
- abs. value > Absolute value greater than.
- = Equal to
- Not equal to
- is in list Matches at least one of the entries in the list.
- is not in list Does not match any entry in the list.
Terms in lists can be comma, semicolon, or space separated.
Number formatting and filter criterion: The number of digits to display after the decimal separator (fractional digits) can be set in the CLC Main Workbench Preferences. Thus, there may be more digits in a number stored in a table than are shown in a view of that table. For this reason, we recommend using operators that do not require exact matches, such as =, when filtering on non-integer values.
Saving and reusing table filter sets
Sets of filter criteria defined in the advanced filtering area at the top of a table can be saved for re-use. Filter sets can also be exported and imported, supporting sharing of filter sets with others.
Options for saving and managing filter sets are provided in a menu revealed when you click on the Filter Sets... button (figures 9.6 and 9.8). Saved filter sets are also listed here (figure 9.7). Selecting a saved set from this menu will add those conditions to the top of the table, and apply the filtering.
Saved filter sets can also be applied from the Manage Filters dialog (figure 9.8).
Figure 9.1: Selecting Save Filters from the menu under the Filter Sets... button (top) opens a dialog showing the filter criteria and prompting for a name for the filter set (bottom).
Figure 9.2: Saved filter sets are listed at the bottom of the drop-down menu revealed when you click on the Filter Sets... button.
Figure 9.3: Selecting Manage Filters from the menu under the Filter Sets... button (top) opens the Manage Filters dialog, where saved filter sets can be applied to the open table, or deleted. Functionality to export and import filter sets is also provided here (bottom).