220.127.116.11 Worksheet Query
This tutorial will show you how to use the Worksheet Query dialog.
Minimum Origin Version Required: Origin 8.5.1 SR0
What you will learn
This tutorial will show you how to:
- Extract numeric and time data
- Use an alias in an extraction condition
- Use LabTalk functions in an extraction condition
Start with a new workbook and import the file \Samples\Statistics\body.dat, click the menu item Worksheet: Worksheet Query to open the dialog as follows:
Basically, there are two main panels in the Worksheet Query dialog. The left panel lists all the columns in the active worksheet, you can right-click and select some column properties you want to see, such as Format, 1st Value, etc.
Note the Extract column in this panel, only data selected in the Extract checkbox will be extracted.
The right panel is where you set and test extract conditions. For example, you can select the column you want to use in the extract condition, and then click the button to move it into the Select Column Variable for If Test group.
Set the conditions
Extract Numerical Data
When there are available columns in the Select Column Variable for If Test group, the Condition edit box becomes editable for you to set conditions. For example, select height and weight to the group, Origin will automatically set an alias for each column. You can click into the Alias cell and rename the alias:
These alias can be used directly in the extract condition. Let's keep the default alias, h and w in this example.
The buttons on the right side of Condition edit box can be help to establish extract conditions. For example, to extract data that is no less than 160cm, highlight the column on Select Column Variable for If Test and click Add... button and build the first condition as follow:
Click OK to close the dialog. When there are multiple conditions, you can also combine these conditions by logical operation using AND, OR, NOT buttons. Now click AND button and then highlight w on Select Column Variable for If Test group and click Add... button again, this time, we are looking for weight no greater than 50kg:
When the condition is done, click All Rows button to select all rows, then click the Test -- select if true button and Origin will return 5 found records. Of course, if you familiar to logical operation syntax, you can type the condition on the edit box directly:
h>=160 AND w<=50
Accept other default settings and click the OK button. A new workbook is created with these 5 records.
When extracting strings, you need to enclose the string by double quotation marks ". For example, select the gender column into the Select Column Variables for If Test group. Using the alias g, you can extract all female data by:
g == "F"
Extract Time Data
Date and Time data are internally saved as numeric values in Origin. Date is the integer part of the numeric value, while Time is the fractional part. In Origin, you can use the int() and frac() functions to return the integer and fractional part of a number, and use the Date(MM/DD/YY) and Time(HH:mm:ss) functions to transfer string to time data. We can combine these functions to extract time data.
For example, using data from Import Time Data tutorial, you can extract data within time period 10:00 ~ 11:00 by:
frac(B) > Time(10:00:00) AND frac(B) < Time(11:00:00)
You can see Origin found 120 records. Similarly, if you want to extract Date data, you can try some condition like:
int(A) > Date(01/24/2004)