The Filter operation allows users to search for values in a dataset, specify if only matching data are returned (include), if the matches are filtered out (exclude), or replaced with another value. Additional options allow users to choose if the entire dataset is to be searched or just a particular column and if the entire row containing a matching cell is filtered or just the matching cell.
Main Mappings panel
Options tab
Users must map a dataset to the Main Mappings panel when the first step is set up. All subsequent steps will automatically be mapped to the same dataset.
Use the Main Mappings panel to specify which input variables are filtered. Main Mapping input variables to context associations is optional.
None: Columns mapped to this context are simply included in the output.
Exclude: Columns mapped to this context are excluded in the result worksheet.
To specify the type of filter
Select one of the option buttons to choose the filter type.
Built In – Use the Filter Specification dialog to add a filter.
Selection (Exclude) – Use the Filter Selection dialog to exclude part of a dataset.
Custom (Include) – Use the Custom Filter Entry dialog to specify a custom filter.
Click Add to add a filter.
The Specify Filter field lists all added filters. Select a filter to remove, edit, or move it.
Filters are executed in the order they are listed.
Click Undo to remove the last filter created from the list.
Click Edit to edit a filter.
Click Move Up to move a filter to top of the list.
Click Move Down to move a filter to the bottom of the list.
Note:If a User Selection filter is defined and listed in the Specify Filter field, the Move Up/Move Down buttons in the Data Wizard are disabled.
When the Built In option is selected in the Options tab, clicking Add displays the Filter Specification dialog.
In the Action menu, select a filter action.
Exclude: Filter out cells/rows that match the filter criteria.
Include: Keep only the cells/rows that match the filter criteria.
Replace: For each cell that matches the filter criteria, replace it with a specified value.
From the Column menu, select the column to which the filter criteria is to be applied.
From the Operator menu, select an operator.
=: equal to the search string
<: less than the search string
>: greater than the search string
≤: less than or equal to the search string
≥: greater than or equal to the search string
≠: not equal to the search string
Note:To exclude (include) “not equal” values, instead include (exclude) equal values.
Select the name of an existing column from the Select Column or Enter Value pull-down menu to compare its cell values with those in the column chosen in the Column pull-down menu.
Or
Type a value directly into the field to search for that value in the column selected in the Column pull-down menu.
If Exclude or Include is the action, check the Apply to entire row box to exclude/include the entire row when the filter criteria is matched. or clear the checkbox (the default) to exclude or include a cell that matches the criteria.
If Replace is the action:
In the Replace with field, type the value to place in a cell that matches the filter criteria.
Check the Replace All box to replace all instances where the criteria is matched in the specified column (or in all columns if Any is specified). Clear the checkbox to only replace the first match in the specified column (or first match in each column if Any is specified).
Type a value in the Tolerance field to search for values within a range of a numeric search value.
The Tolerance field is only available when a numerical value is entered in the Value field and is used to find values not different from the numeric search value by more than the tolerance value. This function is optional.
Numeric values that are +/- the tolerance of the search value are considered to match the search value. If a non-numerical value is entered in the Find field, then the Case Sensitive checkbox is made available.
Select the Case Sensitive checkbox to search for strings that match the capitalization used in the search string.
Built-in filter rules:
Blank cells can be filtered by using the term ‘NULL’ in the filter expression to represent the blank cells.
Users can filter cells that contain values with leading single quotes by typing an additional single quote before the value in the Filter Specification dialog. For example, to filter a value like ‘000123’, users need to type “000123” in the Custom Filter Entry dialog.
When the Selection (Exclude) option is selected in the Options tab, clicking Add displays the Filter Selection dialog.
Note:If this table is blank, click Cancel, check the Retain Intermediate Results checkbox in the Options tab, and then click Add. The table will be populated.
Use the pointer to select part or all of a worksheet.
Click OK to exit the Filter Selection dialog, or click Cancel to exit the dialog without excluding the selected cells.
Note:Adding a User Selection filter to the list in the Specify Filter field results in the Move Up/Move Down buttons in the Data Wizard becoming disabled.
This section contains the syntax and some of the codes that can be used to create custom inclusion filters for a worksheet. Any standard SQL syntax can be used. Custom filters are defined in the Custom Filter Entry dialog.
In the Custom Filter Entry dialog, type the custom filter.
Click OK to add the filter or click Cancel to exit the dialog without adding the filter.
Only one filter can be added at a time in the Custom Filter Entry dialog.
Custom filter syntax:
[Column name] [Expression] [Value] [Logical operator][Column name] [Expression] [Value]
Example: Subject='GW' AND Time <= 12
Logic operators:
Statement grouping is done using the Boolean AND, OR, and NOT operators. Use parentheses to group clauses and force precedence. The AND operator has precedence over other operators.
Example: (Subject='JDW' OR Subject='LEJ') AND Conc > 1
When creating comparison expressions, the following operators are allowed:
=: Equals
<>: Not equals
<: Less than
<=: Less than or equal
>: Greater than
Custom filter rules:
-
User-defined values can be used in expressions to compare against column values.
Example: Effect <= 75.00 -
String values must be enclosed in single quotes.
Example: Subject='JDW' -
String values and column names are not case sensitive.
-
Date values should be enclosed with pound signs (#).
Example: Date > #12/01/2008# -
Decimals and scientific notation are permitted for numerical values.
-
Blank cells can be filtered by using the term ‘NULL’ in the filter expression to represent the blank cells.
-
A wildcard can be specified by a percent sign with the operator ‘like’.
Example: Analyte like ‘Metabolite%’
