4.4.17.1 Worksheet Query

Wks-Query

The Worksheet Query menu command directs Origin to locate data in the active worksheet based on a user-specified conditional expression. The located data values are either (a) extracted (i.e. values are copied and pasted) to another worksheet, or (b) the cells meeting the condition are selected or are filled with a specified color.

There are two main panels (we will refer to as the "left panel" and "right panel"), plus a menu bar with File (saving and loading queries), Scripts (for pre-processing of data) and Function (for inserting functions into your expression).

Simple Tutorial

1. Use the Import Wizard to import the file \Samples\Data Manipulation\US Metropolitan Area Population.dat. The destination worksheet contains four columns: Population, Sq.Mi., Density, and Metropolitan Area.
2. Select Worksheet: Worksheet Query from the Origin menu to open Worksheet Query dialog box.
3. Clear the check box before Sq.Mi. in the left panel so that this column will not be included in the extracted result.
4. Enter the following condition formula in the Condition box. Note: You can click the AND button instead of typing out A-N-D):

   C>100 AND D[i]$ == "* CA*"

   This condition looks in column Short Name = "C" for densities above 100, for areas in California ("CA") in column Short Name "D". Note that this tool supports wildcard symbols such as * inside strings.

5. Click the Test -- select if true button. Notice that the number of found rows under the Condition box has been updated, and some rows in the worksheet are highlighted.
6. Select the Extract to New Worksheet radio button for Output, and then click the OK button. Note that the extracted results are copied in a new worksheet in the source workbook and that the "Sq. Mi." column that we excluded in step 3. was not included in the output.

Prior to Origin 2019b, you used an Alias rather than a Name to refer to a column of data. Aliases can still be used -- and when Spreadsheet Cell Notation is OFF in the workbook, they must be used. See Notes on Spreadsheet Cell Notation, Use Alias and Column Naming for more information.

Opening and Running the Worksheet Query dialog box

To open the Worksheet Query tool:

1. Activate the worksheet.
2. From the menu, choose Worksheet: Worksheet Query.

After entering the condition in the Condition box, click the OK or Apply button to perform the query.

Left Panel

On the left panel, you can specify the worksheet(s) and columns on which the worksheet query is applied.

Multiple Worksheets

When you have multiple worksheets of similar structure (arranged in one workbook/folder/project), you can check this checkbox to query all these sheets by the same condition(s), extract the values that satisfy and put them together (in one workbook/sheet).

• Check Multiple Worksheets to show the worksheet box. Select the sheets you want to apply query to.

• If Multiple Worksheets is checked, Match by Long Name checkbox (next to Columns) is available. Check it to scout and match columns by Long Name.

• Uncheck Multiple Worksheets to apply query on current worksheet only.

Columns

For single workhsheet, this box lists all the columns in the active worksheet, with some basic information about them. If Multiple Worksheets is checked, it lists columns in the first sheet.

Check or clear the boxes in Extract column to determine which columns will be extracted.

You control which information displays for each column by right-clicking in the box and selecting or enabling components from a shortcut menu.

Right Panel

The right panel includes the condition edit box, output controls, and other controls.

Select Column Variables for If Test

This portion of the dialog box is only shown if either of the following is true (see):

• Spreadsheet Cell Notation is turned OFF in the workbook. When this is the case, you will see this icon in the upper-left corner of the workbook.
• Spreadsheet Cell Notation is turned ON in the workbook but you have opted to check the Use Alias box in the upper-right corner of the Worksheet Query dialog box.

If using Aliases, select your columns in the left panel then move them into the Select Column Variables for If Test list using the button. Moving columns over creates an Alias that can be used in place of a column Name or Index, when defining your Condition.

Condition

The Worksheet Query tool is used to select, extract or color-mark data in your worksheet according to some conditional expression that you define. Type your conditional expression into the Condition text box.

• When building conditions, you can use a worksheet column alias, column Short Name, or Long Name (Col("Long Name")) when referring to a worksheet column in your expression.
• To indicate a row number, use the variable i.
• To continue an expression on the next line, press Enter.

For example, to extract rows with values in column A that are greater than 0, you can type:

A>0

or

Col(A)>0

If you have specified an alias, for example "Temperature" for column A, you can also use:

 Temperature > 0

To query worksheet data using text criteria, use the following syntax:

Col(ColumnName)[i]$ == "text";

For example:

Col(Station)[i]$ == "Northwest";

Allowed Operators

You can use any of the logical and relational operators listed below in building your expression. Additionally, you can use any arithmetic operators (+, -, *, /, ^).

Buttons

Use of these buttons is optional (you can type elements directly into the Condition box) but using them may save some keystrokes.

Button	Usage
	Used to add a column variable and condition to your expression. If you are using Aliases, highlight the Alias/Column in the Select Column... field above before clicking Add and the Alias will register in the Column Variable field.
	Highlight any or all of the expression in the Condition box, then click Remove to delete.
	Adds "AND" to your expression.
	Adds "OR" to your expression.
	Adds "NOT" to your expression.
	Adds a left-paren to your expression.
	Adds a right-paren to your expression.
	Click this button to test the condition. If records are found that match the condition, the total number of result rows will be displayed behind Rows Found: in the Worksheet Query dialog and the corresponding rows will be highlighted in the source worksheet.

Row(i): From - To -

Specify the range of rows to be extracted.

Output

• Recalculate control.

  This drop-down list is used for specifying how to update the results when the source data or conditions are changed: None for no update, Auto for updating automatically and Manual for updating manually. For more information about Recalculate, please refer to Recalculating Analysis Results

• Specify what will be output if the condition holds:

The Dialog Menus

File

Scripts

Set LabTalk scripts that will be run before the data query. This will help to prepare proper data for the condition. You can set a Script Before Select Data Loop and a Script Before If Condition.

Origin actually runs the data query routine as follows:

1. Run the Script Before Select Data Loop.
2. Loop over all rows defined by the start and end values provided by user. For each column whose row index is i:
   1. Run the Script Before If Condition ;
   2. Test the condition, for data in row i;
   3. Extract data if the condition is true;
   4. End query;
3. End loop;

Suppose there are two columns. Col(a) has some data. Col(b) contains three numbers which will be used as row indices of cells which will be extracted from Col(a).

We can set the Script Before Select Data Loop as follows:

j=1;

Then set the Script Before If Condition as:

if( i == col(b)[j] )
{
	temp = col(a)[i];
	j++;
}

Extract data in the worksheet with the following condition:

A == temp

After clicking the Test button, Origin will highlight row 1, 4, and 7.

There is a quick way to load a conditional control or loop script, when you are doing script in Script Before Select Data Loop box or Script Before If Condition box. Right click on the edit box to select Conditional/Loop at the bottom of the context menu, and then select a conditional structure or loop you desired in the flyout. The syntax will be added at cursor with simple comments.

Function

You can insert functions from a number of categorized lists, to build your query condition. Recently used functions are accessible from the Recent Used sub-menu. This Function menu works in the same way as it does in the Set Values dialog .

These functions include: LabTalk functions and built-in Origin C functions.

To add a user-defined function in the Function selection, please refer to the How to Create a User-Defined Function for Set Column Values.

For more information of these functions, please refer to LabTalk Supported Functions.

You can also use predefined variables or a constant in your query condition, from the Predefined Variables sub-menu. See here for available predefined variables and constant.