Worksheet 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 extracted. In other words, these values are copied and pasted to another worksheet, or, the cells that have these values are filled with a specified color or selected.

There are two main panels in this dialog: the left panel and the right panel.

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. With the ctrl key down, select Density and Metropolitan Area in the left panel. Click the => button to make these columns available for constructing the condition. Note that aliases D and M are assigned to the columns respectively.
4. Clear the check box before Sq.Mi. in the left panel so that this column will not be included in the extracted result.
5. Enter the following condition formula in the Condition box. Note: You can click the AND button instead of typing out A-N-D):

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

   This condition looks for densities above 100 for all areas in California ("CA"). Note that this tool supports wildcard symbols such as * inside strings.

6. 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.
7. Select the Extract to New Worksheet radio button for Output, and then click the OK button. You see that the extracted results are stored in a new worksheet in the source workbook.

Opening and Running the Worksheet Query dialog box

To query data from a worksheet, you can use the Worksheet Query tool. To bring up this tool, you can:

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

The left panel lists all the columns in the active worksheet, with some basic information about them. You can check before the columns to select those that will be extracted. To see more information about the column, right-click on the panel area and select the quantities you want to see in the left panel. Menu commands available in the short-cut menu include the following:

Right Panel

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

Select Column Variables for If Test

Before querying data, you should select columns into this list, and these columns will be used to build the condition. To move columns to the list, select them on the left panel and then click the button. Only when there are columns in this list is the Condition edit box editable.

There are two items on this table:

Condition

Type a conditional expression into the text box used to extract data. You can use any of the logical and relational operators listed below. Additionally, you can use any arithmetic operators (+, -, *, /, ^).

Besides typing the conditional expression, you can also use the buttons beside the Condition edit box to build the condition.

When building conditions, you can use both the worksheet alias and Col() notation to specify a column in the 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:

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";

Test -- Select if True

Click this button to test the condition. If any record is found, the total number of result rows will be shown 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.

F(x)

You can insert functions from a number of categorized lists, to build your query condition. Recently used functions are accessible from the Favorite submenu. This F(x) menu works the same to the one in the Set Values dialog.

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

To add a user-defined function in the F(x) 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 Script Accessible Functions in Appendix 3.