Filtering Data

INFO

Datameer X provides a feature to filter data on a worksheet by specified criteria. Additional filters can be added as well as regular expressions or formulas can be used.

General Data Filtering 

INFO

When filtering data from a workbook sheet, you can choose between:

  • creating a new read-only sheet to display the results
  • filtering the data directly on the current sheet

The options available to filter vary based on the type of data in the column selected.

A filtered worksheet is based on sample data defined from the source of the data. When first creating a filtered worksheet, the displayed data is based on the current data sampling. After the workbook has been run, the joined sheet calculations are applied to the full data.

INFO

When creating a filtered sheet that displays no data, Datameer X informs you that the empty sheet is displayed on the current sample data. Running the workbook again creates a new sampling for the sheets that take into account the calculation of the filtered sheet.

Filtering Data by Using Formulas

Creating a Filter

To filter data:

  1. Open the required workbook and the required sheet. The workbook content is displayed.
     
  2. Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens. 
     or 
  3. Mark the checkbox when you want to create the filter in a new sheet.
  4. Select the filter column from the drop-down.
  5. Select the expression from the drop-down. 
  6. Enter the value. 
  7. Select the required filter-argument from the drop down. 
  8. If needed, click "+" to add a filter.
    INFO: Clicking the "-" deletes the respective filter on the left.
  9. If adding further more filter, select the next filter column, expression, value and filter-argument. 
  10. Select the way multiple filters are connected with. 
  11. Confirm with "Create Filter". The filter is created and applied to the sheet. 

Using the Advanced Filter

INFO

Advanced filter formulas are similar to those used in a workbook's formula bar though without column name completion and less error handling. You can create a formula that combines multiple conditions using multiple referenced columns or use nests functions and constants.

Text-based expressions must return a Boolean value and are required to reference columns in the current sheet. When the expression that is applied to the current record returns 'false', the record is dropped, otherwise it remains.

TIP

Datameer X recommends developing and testing your advanced filter formulas in a separate copied worksheet before applying to your actual data.

To filter data with the advanced filter:

  1. Open the required workbook and the required sheet. The workbook content is displayed.
     
  2. Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens. 
     or 
  3. Mark the checkbox when you want to create the filter in a new sheet.
  4. Select the tab "Advanced". 
  5. Enter the filter criteria from the available expressions.
    Example: (#A > 70 && #B == false) || (#A < 30 && #B == true)
    INFO: Multiple operators can be used to create an advanced filter.  
  6. Confirm with "Create Filter". The filter is created and applied to the sheet. 

Updating a Filter 

To update a filter from an already filtered sheet:

  1. Click on "Edit" and select "Filter" or click the "Filter" icon from the icon bar. The dialog 'Apply Filter' opens. 
     or 
  2. Change the filter criteria to your needs and confirm with "Update Filter". The changes apply to the sheet. 
     

List of Filter Expressions

INFO

Find the needed filter expressions within the list.

Data TypeExpression NameExpression Description
String/ Number/ Date/ ListequalsThe value entered exactly matches a value in the column specified.
String/ Number/ Date/ Listdoes not equalThe value entered does not exactly match a value in the column specified.
String/ Number/ Date/ Boolean/ ListcontainsThe value entered is present in the column specified.
String/ Number/ Date/ Boolean/ Listdoes not containThe value entered is not present in the column specified.
Stringends withThe value entered match starting with the last characters in the specified column.
Stringnot ends withThe value entered does not match starting with the last characters in the specified column.
Stringstarts withThe value entered match starting with the first characters in the specified column.
Stringdoes not begin withThe value entered does not match starting with the first characters in the specified column.
Stringmatches regexpThe regular expression value matches a regular expression in the specified column.
AnyblankDisplays the data where the value of the specified column is empty.
Stringnot blankDisplays the data where the value of the specified column is not an empty string.
String/ Numbercontained inThe value entered with quotation marks " " includes the entire data from a cell in a specified column.
String/ Numbernot contained inThe value entered with quotation marks " " does not include the entire data from a cell in a specified column.
String/ Number/ Date/ List/ Booleanis empty cellDisplays the data where the value of the specified column is <null>.
String/ Number/ Date/ List/ Booleancell not emptyDisplays the data where the value of the specified column is not <null>.
NumberlessDisplays numbers less than the number entered.
Numberless equalsDisplays numbers less than or equal to the number entered.
NumbergreaterDisplays numbers greater than the number entered.
Numbergreater equalsDisplays numbers greater than or equal to the number entered.
DateafterDisplays dates for the specified column after the date entered.
Dateafter equalsDisplays dates for the specified column after or equal to the date entered.
DatebeforeDisplays dates for the specified column before the date entered.
Datebefore equalsDisplays dates for the specified column before or equal to the date entered.
Listis empty listDisplays lists that have no elements from a specified column.
Listnot an empty listDisplays lists that contain elements from a specified column.
BooleantrueDisplays boolean values that are true from a specified column.
BooleanfalseDisplays boolean values that are false from a specified column.

Example for Advanced Filters

CONTAINS(#a;"500") || ISBLANK(#a) || !CONTAINS(#a;"800") 

This example can be read like this:

"Filter all entries that are contained in the column named 'a' with the value '500' OR filter entries where the value of the column 'a' is empty OR filter entries from column 'a' which do not contain the value '800'."Â