OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis     
 
Skip Navigation Links
All BooksExpand All Books
Origin HelpExpand Origin Help
Workbooks Worksheets and Worksheet ColumnsExpand Workbooks Worksheets and Worksheet Columns
Simple Manipulation of Worksheet DatasetsExpand Simple Manipulation of Worksheet Datasets

4.4.11 Data Filter

Discription

DataFilter.png

Origin offers data filters of three data formats, date time, numeric, and text. The data filter could be added, removed, enabled, disabled, or reapplied by button click in the Worksheet Data toolbar and customized with user defined filtering conditions.

Add/Remove Data Filter

To add or remove a data filter to the one or several columns:

  1. Highlight the desired column(s).
  2. Click the Add/Remove Data Filter button Button Add Remove Column Filter.png.

When a data filter is added, it is by default an empty filter, no filtering condition is set. Once filtering conditions are set, the filter is automatically named by the filter condition and the filter icon is fully filled with color green.

Column Filter 001.png

In order to remove a data filter, you could also:

  1. Highlight the desired column(s) with data filters.
  2. Click the Filter icon and choose Clear Filter in the context menu.

Column Filter 002.png

When a data filter is removed or cleared, it is not possible to re-use the filtering conditions again.

Enable/Disable Data Filter

When a data filter is added, it is possible to enable or disable it by either of the following:

  1. Highlight the desired column(s).
  2. Click the Enable/Disable Data Filter button Button Enable Disable Column Filter.png.

or

  1. Highlight the desired column.
  2. Click on the Filter icon and bring up context menu, check Enable Filter to enable and uncheck to disable.

Column Filter 003.png

If multiple columns are selected in the second case, only the data filter of the leftmost selected column will be disabled/enabled.

When a data filter is disabled, the filter icon turns to grey, and there is an "off" before the filter name.

Column Filter 004.png

If your selected range of columns include both enabled and disabled columns, when you click the Button Enable Disable Column Filter.png button, all columns will be disabled.

Reapply Data Filter

In case the data is updated or filtering condition is changed, you could click the Reapply Data Filter button Button Reapply Column Filter.png to reapply the data filter.

Customize Data Filters

Origin will detect the data type and automatically assign one of the three data filters (date, numeric, text) to the corresponding columns. The filtering conditions need to be set when customizing data filters.

There are two ways to customize a data filter,

  1. Click on the Filter icon to bring up a context menu.
  2. For each data filter type, several quick menu items are available, choose one to open the corresponding Simple Filter dialog.
  3. Set data filter conditions and click OK.

or

  1. Click on the Filter icon to bring up a context menu.
  2. Select the Custom Filter to open the Custom Data Filter dialog.
  3. Set data filter conditions and click Apply or OK.

Column Filter 005.png

Menu Options and Dialog Control

There are three types of column filter, for each column type, the corresponding quick menu items and Custom Data Filter dialogs are different.

Date Filter

Date Time Filter will be used when the data format is set to Date.


The quick menu provides several options:

Equals Bring up the Simple Date Filter dialog with default formula type as equal and value as the current date.
Before Bring up the Simple Date Filter dialog with default formula type as before and value as the current date.
After Bring up the Simple Date Filter dialog with default formula type as after and value as the current date.
Between Bring up the Between dialog with default from and to value as the minimum and maximum of the data set.

The Simple Date Filter has the following options for formula types:

equals Keep the rows where the date and time is exactly the same with query date and time, and hide the others.
does not equal Keep the rows where the date and time is not the same with query date and time, and hide the others.
after Keep the rows where the date and time is later than the query date and time, and hide the others.
on or after Keep the rows where the date and time is not earlier than the query date and time, and hide the others.
before Keep the rows where the date and time is earlier than the query date and time, and hide the others.
on or before Keep the rows where the date and time is not later than the query date and time, and hide the others.

The Value box is used to specify the querying date and/or time, the default is the local date and time in the computer.It is possible to set the date value both with calendar or direct typing.

The Condition 2 specify the logical relationship in case two query conditions are set, and has the following options:

None The second filter condition will not be used.
And The second filter condition will be used. Keep the rows where the date and time holds true for both query conditions, and hide others.
Or The second filter condition will be used. Keep the rows where the date and time holds either one of the two query conditions, and hide others.

You can do advanced setting of filter conditions with the Custom Data Filter dialog, you can click the Custom Filter menu item to bring it up.

Numeric Filter

The numeric filter will be applied when the selected column contains numeric data.

The quick menu provides several options:

Equals Bring up the Simple Numeric Filter dialog with default formula type as equal and value as the first value in current column.
Less than Bring up the Simple Numeric Filter dialog with default formula type as is less than and value as the first value in current column.
Greater than Bring up the Simple Numeric Filter dialog with default formula type as is greater than and value as the first value in current column.
Between Bring up the Between dialog with default from and to value as the minimum and maximum of the data set.
Top 10 Bring up the Top N dialog with default filter condition as Top 10 items.
Bottom 10 Bring up the Top N dialog with default filter condition as Bottom 10 items.

The Simple Numeric Filter has the following options for formula types:

equals Keep the rows where the cell value equals the query number, and hide others.
does not equal Keep the rows where the cell value doesn't equal to the query number, and hide others.
is greater than Keep the rows where the cell value is bigger than the query number, and hide others.
is greater than or equal to Keep the rows where the cell value is bigger than or equal to the query number, and hide others.
is less than Keep the rows where the cell value is smaller than the query number, and hide others.
is less than or equal to Keep the rows where the cell value is smaller than or equal to the query number, and hide others.

The Condition 2 specify the logical relationship in case two query conditions are set, and has the following options:

None The second filter condition will not be used.
And The second filter condition will be used. Keep the rows where the date and time holds true for both query conditions, and hide others.
Or The second filter condition will be used. Keep the rows where the date and time holds either one of the two query conditions, and hide others.

The Custom Data Filter dialog will be brought up by clicking the Custom Filter menu item and used for advanced filter condition setting.

Text Filter

The text filter will be applied if the data format of the selected column is Text, Month or Day of Week.

From the quick menu, you can select/deselect the check boxes for corresponding text entries to show/hide them.

Column Filter Menu 002.png

Click the Custom Filter quick menu item to bring up the Custom Filter(Simple Text) dialog.

Column Filter Menu 003.png

The Entry column lists all the unique text entries from the selected range, the Count column lists the frequency of the corresponding text entry in the selected range.

Clicking on either column header will sort the results in data panel by ascending or descending order.

The check boxes on the left are used to specify whether to hide the rows with the corresponding entry or not. When it is unchecked, the corresponding rows will be hidden.

There are three buttons on the right:

Select All All check boxes will be checked.
Toggle All The status of all check boxes will be reversed, so the checked boxes will be unchecked and the unchecked boxes will be checked.
Clear All All check boxes will be unchecked.
After using the Text Filter, the Filter label row will contain a list of the text entries which have not been hidden with the filter. By default, the entries in this cell are separated by a Space. The system variable @TFS can be used to switch the separater: 0=Enter, 1=Space, 2=Comma, 3=Semicolon.

In addition, there is another system variable @TFL which can be used to set the max length of text filter label. Its default value is 50. The first string and last string will always show and the rest of strings will be shown as "...".

Select the Advanced check box to bring up the Custom Filter(Advanced Text) dialog.

Column Filter Menu 004.png

The condition panel is used to input filter conditions, you can click the triangle button to bring up the fly-out menu to call text related functions.

The before condition script panel is used to define Labtalk script which will run before the filtering.

i can be used in the Condition panel to substitute for the row index, for example, i>15 means the rows after 15th row. It also can be used in the Custom Data Filter dialog.

Wildcard control is supported, the following tables provides some tips of using the advanced text filter to make your work more efficient.

Usage of special characters in the advanced panel of text filter
Symbol Usage
 ?(question mark) Stands for any single character, e.g. "a?c" finds "abc" or "adc" but will not find "abbc"
*(asterisk) Stands for any string of characters, e.g. "abc*e" finds "abcde" or "abcdde" or "abce"
==(two equation marks) Stands for full match, e.g. x=="a*" finds exactly "a*" but not "abc"

The following short tutorial will show you how to use the wildcard control for text filter.

  1. Import the Automobile.dat file from \Samples\Statistics\ into Origin.
  2. Highlight column B and click the Add/Remove Data Filter button Button Add Remove Column Filter.png.
  3. Click on the Filter icon and select Custom Filter.
    Column Filter 006.png
  4. Check for the Advanced check box.
  5. In the Query box, enter:
    x LIKE "S*"
  6. Click OK and then Yes for the reminder message(if any).
  7. Go back to the original worksheet, only the rows with text starting with "s" remain.

Custom Data Filter dialog

The Custom Data Filter dialog is used to do advanced setting of filter conditions of Date and Numeric filter.

Column Filter Menu 001.png

The condition panel is used to input filter conditions, you can click the triangle button to bring up the fly-out menu to call date/numeric related functions.

The before condition script panel is used to define the Labtalk script which will run before the filtering.

There are four buttons available:

Test The rows which meet the filter condition will be highlighted in the original worksheet, these rows will remain after filtering.
OK Apply the change of filter conditions and close the dialog.
Cancel Close the dialog without applying the modification of filter conditions.
Apply Apply the change of filter conditions without closing the dialog.

For the numeric filter, some built-in Labtalk functions are not included in the fly-out menu, this table below provides implemented information of these functions:

Usage of some Labtalk functions in the numeric filter
Expression Usage
x.between(x1,x2) Return to the sub range of x between user-input values x1 and x2, equal to
x<=x2 && x>=x1
.
x.top(n,0/1) Return to the biggest n values of x; when 0 is chosen, n is the number of item, when 1 is chosen, n is the percentage.
x.bottom(n,0/1) Return to the smallest n values of x; when 0 is chosen, n is the number of item, when 1 is chosen, n is the percentage.


For the Text/Numeric/Date filter, Origin also supports the function x.count() to count the number of the duplicated data.

For example: To keep text data which number is over 3, in the Custom Data Filter dialog you can set:

Condition: x.count() > 3

Ignore Hidden Rows

It is possible to choose whether to consider the rows hidden by a data filter in data analysis and/or graphing. By default, the hidden rows will be ignored. But in order to not ignore hidden rows in data analysis and/graphing, either:

  1. Activate a worksheet.
  2. In command window, run the script:
wks.ignorehidden = 0;

or

  1. Select from top menu Format:Worksheet or press F4 to open the Worksheet Properties dialog.
  2. In the Miscellaneous tab, uncheck Ignore Hidden Rows on Plotting and Analysis.
Note: For LT access and Set Values, we will always not ignore hidden rows.

When apply mask and add data filter on the column data, the masked data is still shown on the column by default.

Whether showing the masked data on the column with data filter, that is controlled by the system variable @FBM.

  • 0:Hide
  • 1:Show

Save Data Filter into Operation

In the Recalculate lock icon's context menu for Copy Columns to... and Pivot Table, there are three worksheet filter options. They are used to control whether the results will be affected by further filter changes.

Note: Recalculate Mode of Copy Columns and Pivot Table should be set to Auto or Manual.

Filters Lock.png

Worksheet Filters:Lock

When this option is selected, the result will be locked from data filter condition change of source column(s). So when the filter condition in the source column worksheet is changed, it will not trigger update in the result columns.

Worksheet Filters:Reload

This option is only available when the Worksheet Filter: Lock has been selected already. It reloads the data filter condition from the source column(s) to the result column(s). i.e. after you changed the data filter condition of the source column(s), click this option to trigger the auto update of the locked result column(s), so that the same filter condition applies to result column(s) as well.

Worksheet Filters:Push Back

This option is only available when the Worksheet Filter: Lock has been selected already. It pushes the initial data filter condition back to the source column(s). i.e. after you changed the data filter condition of the source worksheet, click this option and the most recent data filter condition that has been applied from source column(s) to result column(s) will be pushed back to the source worksheet. Note that if you applied a data filter directly to result column(s), it will not be pushed back to source.

 

© OriginLab Corporation. All rights reserved.