You can define a subset of rows using a selection expression, a mathematical formula that evaluates to 1 (true) or 0 (false).
To select rows using a selection expression:
- In an open table or worksheet, click
The Select Rows (Advanced) dialog is presented.
- (required) In the Expression text box, enter a selection expression, which will be used to define a subset of rows. The selection expression may refer to one or more columns and may include standard arithmetic, relational, and logical operators as well as any of 1010data's functions.Note: When referring to a column in an expression, use the column name as opposed to the column heading. A list of all the column names for the table appears at the bottom of the Select Rows (Advanced) dialog.
For example, to select rows where the transaction ID (transid) is between 535 and 540, use the selection expression between(transid;535;540).
Generally, each operator and function in the selection expression deals with N/A values in a reasonable way, but see the description of each operator and function for details. For instance, price<100 is 0 (false) for any N/A values in the price column; to select rows where a column is (or is not) N/A, use a value of NA (e.g., price<>NA).
- (optional) Select Treat special values as NA? if you want special values treated as N/A in calculations.
Although the majority of table columns do not have special values, some columns have special values that represent a kind of missing value. Special values are specified when the data is initially loaded into 1010data. To find out if a column has special values, show the column information for that column (or for all columns).Note: There is a performance degradation associated with selecting this option, so it is best to not select it unless special values are, in fact, present.
In a Price column, for instance, a value of 999 might indicate that the price was unavailable, while a value of 888 might mean that it was not applicable. In both cases, the values would be treated as N/A rather than used in calculations if this option was selected.
- Select Keep the current row order? if you want to display the selected rows in the same order as they appeared in the original table or worksheet. If this check box is clear, the rows in the resultant selection will appear in an arbitrary order.Note: For very large tables, clearing this check box can noticeably speed up the selection.
- Click Select. Only those rows for which the selection expression evaluates to true will be displayed. Also, if you summarize the data, only the selected rows will be included in the summary.Note: The selection applies only to your session and does not affect the original table.
If you do several row selections in succession, each selection is applied to the previously selected rows. With each selection, the number of rows will decrease (or possibly stay the same); it will never increase.
The Selections in Effect at the top of the dialog displays every selection currently applied to the worksheet or table. The Number of Rows Selected shows the number of rows remaining after these selections.
To undo all of the selections and return to the original number of rows at any time, click Reset to All.