4.4.19 Data Filter

Wks-DataFilter

Description

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.

You can also apply a data filter to column label row data by switching to Column List View (View: Column List View) and applying a data filter to a List View column. For more information, see Column List View - Applying a Data Filter.

Status of Filter

Once you have added a filter to a column, you can check the filtering status from the color of the filter icon on the column header.

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 .

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.

Clicking the Add/Remove Data Filter button on a selected column with an applied filter, removes the filter. The same thing is done when you clear a filter from the filter icon menu.

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

If you want to toggle your filter on or off without losing your filter, just disable the filter (see next).

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 .

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.

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.

If your selected range of columns include both enabled and disabled columns, when you click the button, all columns will be disabled.

Reapply Data Filter

Like MS Excel, when you make changes to filtered data in a worksheet column, you must reapply the filter. When the filter is in need of being reapplied, the filter icon will turn yellow. You can reapply the filter by clicking the Reapply data filter button on the Worksheet Data toolbar.

Note: We may introduce LabTalk variables into the filter condition. In general, the filter condition with the variables will be cached after the filter is run. Thus, the result will not change after you click the Reapply Data Filter button , even you already change the values of the variables by LabTalk. If we want to update the LabTalk variables in filter condition regardless of the cache, use LabTalk method wks.runfilter() to reapply filter.

By default, when saving a workbook with a Data Connector, imported data are excluded (not saved with the file). If you add a data filter to the workbook, when the file is reopened, the data filter is auto-run after import. If you do not want the data filter to auto-run on import, set @WFI=0. For information on changing the value of a LabTalk system variable, see this FAQ.

Copy and Paste Data Filter

You can copy a data filter from one worksheet column and apply it to other columns. This includes custom filters in which use variables defined in the Before Condition Script panel.

1. Click on the column's filter icon and choose Copy Filter menu item.
2. Select the target worksheet column, add a filter to it if no filter yet. Click on the filter icon and select Paster Filter menu item.

OR,

1. Right-click on the column's Filter cell and choose Copy; or click on the cell and press CTRL+C
2. Select the target worksheet column(s) and press Ctrl+V to paste the filter and apply it to data in those columns.

OR, for numeric column,

1. Click on the column's filter icon and choose Save as menu item. Save the filter settings to a name.
2. Select the target worksheet column, add a filter to it if no filter yet. Click on the filter icon and select Load menu item. Select the saved filter from its sub-menu.

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.

Once you've added filter conditions, you can double-click on the Filter cell to edit the filter.

Menu Options and Dialog Controls

Three general types of filters are supported, grouped by Format:

• Date
• Numeric
• Text, Month or Day of Week.

Available menu options vary by Format. Origin uses the column's Format Properties to determine which menu options to display.

Date Filter

When the column Format = Date, you have the following filtering options:

Clicking Equals, Before or After will open the Simple Date Filter dialog box. Clicking Between provides a simple dialog for setting a date range.

Set the Formula Type, Value and Condition2 or From and To, to filter by date.

The Simple Date Filter Condition2 is used for And/Or filtering. The default state is None but you can set the drop-down to either And or Or and create a second filter condition.

For more advanced filtering, use the Custom Data Filter dialog, available when you click the Custom Filter menu item at the bottom of the Date filter menu.

Previously, Value, To and From used a standard date-picker control which has been replaced by a simple text field. To revert to the date-picker set system variable @DP = 1.

Numeric Filter

When the column Format = Numeric, you have the following filtering options:

Clicking Equals, Less Than or Greater Than will open the Simple Numeric Filter dialog box. Clicking Between provides a simple dialog for setting a numeric range.

Set the Formula Type, Value and Condition2 or From and To, to filter by number.

The Simple Numeric Filter Condition2 is used for And/Or filtering. The default state is None but you can set the drop-down to either And or Or and create a second filter condition.

Clicking Top 10 or Bottom 10 opens the the Top N dialog box. Here you can filter out all but the highest or lowest values by number of Items or by Percent.

For more advanced filtering, use the Custom Data Filter dialog, available when you click the Custom Filter menu item at the bottom of the Date filter menu.

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 to show/hide corresponding text entries.

Custom Filter (Simple Text)

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

• 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 the data panel by ascending or descending order.
• Use check boxes to show or hide the rows corresponding to each entry. Clearing a box hides corresponding rows.

After applying a 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 separator: 0=Enter, 1=Space, 2=Comma, 3=Semicolon. Another system variable @TFL can be used to set the max number of characters in the text filter label row. By default, this value is 50. The first string and last string will always show in full (sorting is alphabetical); unlisted strings will be shown as "...". For information on these two LabTalk System Variables see the LabTalk System Variable List.

When the X column of a column/bar graph contains text, this text is used to label major ticks, ordered by row index. Prior to Origin 2018, when applying a worksheet data filter, plots registered the vacant ticks and labels of filtered data, though the data points were not plotted. This was changed in Origin 2018 so that ticks associated with filtered data no longer display. This only applies to X columns that contain text and are NOT Set as Categorical. You can restore the pre-2018 behavior using wks.KHRA=1; (you could, for instance, add this to the workbook Script Panel).

Custom Filter (Advanced Text)

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

• The Condition panel is used to input filter conditions.
• The Before Condition Script panel is used to define LabTalk script which will run before the filtering operation.

Specifying your Condition

You can use any combination of direct keyboard entry and menu selection to build your expression. The dialog box lists some key elements (wildcard characters, row index) and shows an example expression.

• The Variable to represent Col(N) box lists an editable variable that you must use to represent the current column, when building your expression.
• Click the triangle button to the right to open a fly-out menu. The menu lists String functions, Logical functions and User Origin C functions (if any). Select a function to enter it directly into the Condition box, then modify using your own arguments. For usage information on these functions, see LabTalk Supported Functions.
• You can use natural language logical operators "AND", "OR", "NOT" and "LIKE" or you can use their symbolic equivalents (see Logical and Relational Operators). For example, the following are equivalent ...

Example	Description
NOT(make="Buick" OR make="Chrysler")	Filter out "Buick" and "Chrysler"
!(make="Buick" OR make="Chrysler")	Filter out "Buick" and "Chrysler"
!(make="Buick" || make="Chrysler")	Filter out "Buick" and "Chrysler"

• Usage of wildcards:

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 variable i can be used in the Condition panel as a substitute for row index. For example, i>15 means the rows after the 15th row. Variable i also can be used in the Custom Data Filter dialog.

The following short tutorial will show you how to use the wildcard control for text filter. Import the Automobile.dat file from \Samples\Statistics\ into Origin. Highlight column B and click the Add/Remove Data Filter button . Click on the Filter icon and select Custom Filter. Check for the Advanced check box. In the Query box, enter: x LIKE "S*" Click OK and then Yes for the reminder message(if any). 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 perform advanced filtering of Date and Numeric data.

• Use the Condition panel to input filter conditions. Note that you can click the triangle button to open a fly-out menu for inserting of date- and numeric-related functions. For information on these functions, see LabTalk Supported Functions. For information on operators, see Logical and Relational Operators).

• The Before Condition script panel (if not visible, click the button) is used to define LabTalk scripts to run before filtering. Note, again, the triangle button gives quick access to functions for use in your script.

Custom Data Filter Buttons

For numeric filtering, a few built-in LabTalk functions are not included in the fly-out menu. The following table documents these functions:

x<=x2 && x>=x1

* In this expression, x1 and x2 are typically row numbers. If you wish to use variables in this expression, you should not use "x1" and "x2" as they are widely-used system variables and their values may change. Instead, consider using page variables v1...v4.

For Text, Date, and Numeric filters, 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

Filter Menu

From the Filter menu you can:

• Load samples
• Load saved custom filters
• Save custom filters

Loaded filters display at the bottom of this menu (if none loaded, reads "Empty").

Ignore Hidden Rows

By default, rows hidden by a filter are ignored in graphing operations. You can change this behavior and include such rows using either of the following:

1. Activate a worksheet.
2. In the Command or Script window, run the script:

wks.ignorehidden = 0;

or

1. From the top menu, select Format:Worksheet or press F4 to open the Worksheet Properties dialog.
2. In the Miscellaneous tab, uncheck Ignore Hidden Rows on Plotting and Analysis.

When you apply a mask and add a 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.

You can add a text label to your graph that combines literal text with the current filter condition. For instance in the following example, we combine literal text "Make =" with a string "%(1, @LF)" to create a dynamic label that changes with the change in filter conditions (i.e. note that for the label to update, the filter cannot be locked).