<sel>

Description

<sel> is applied to a table or worksheet of some arbitrary number of rows and produces a worksheet containing only those rows that were not eliminated by the value expression. The expression passed to the value attribute must evaluate to either 0 or 1. For instance, the expression:

sales_price>5.00

where sales_price is a column in a table or worksheet, will be evaluated for each row in the table. If the value of sales_price for that row is greater than 5.00, the expression evaluates to 1 and the row is selected. If the value of sales_price is less than or equal to 5.00, the expression evaluates to 0 and the row is eliminated from the worksheet (but not the physical table).

Expressions without comparisons can also be used for selecting rows. For example, column foo is column containing a 0 or 1, and column bar is a column containing 0 or 1. An expression can be written that will only select the rows where both columns contain a 1, as follows:

<sel value="foo*bar"/>

The selection expression above will only select rows that have the value 1 for both foo and bar.

Syntax

<sel value="[EXPR]"/>

This construction selects the rows for which [EXPR] is true.

Alternate syntax

(Available as of 11.06)

The alternate <sel> syntax allows for selecting a disjunction of multiple boolean expressions by specifying multiple <and> and <or> child elements, as follows:

<sel>
  <or value= [EXPR_1]/>
  <or>
  <and value="[EXPR_2]"/>
  <and value="[EXPR_3]"/>
  </or>
  ...
</sel>

The construction above selects the rows for which either [EXPR_1] or [EXPR_2] and [EXPR_3] are true.

Each <sel>, <and>, or <or> tag may contain zero or more <or> tags or zero or more <and> tags. While <and> and <or> may not be mixed at a given nesting level within a single tag, you may have a complex selection that includes both <and>s and <or>s. In this way, complex selections may be built up from multiple smaller expressions. See the example section.

This alternate syntax may be more convenient for disjunctive selections, especially those constructed by block code. In addition, using this syntax may provide a memory or efficiency improvement compared to evaluating one long expression.

Including a value="[EXPR_0]" as an attribute of the <sel> tag itself is permitted; it will be treated as an additional <or> or <and> clause.

An <and/> or <or/> tag with neither a value attribute nor contents has no effect.

Attributes

value

Accepts a Macro Language expression which is used to filter the current dataset.

spflag

Indicates whether special values should be treated as N/As when evaluating the value expression.

The default is 0 (i.e., do not treat special values as N/As).

expand

Expand the number of rows in a table based on the selection expression.

For <sel value="N" expand="1"/>, where N is a column containing integer values, or an expression (i.e., an expression involving two or more columns that results in a vector of integer values) , expand="1" will create a copy of each row according to the value of N.

For rows where N is 0, the row will still be eliminated.

If N is negative, an error will be returned.

The system variable ii_(B) can be used to provide an enumeration of duplicate rows for each original row after the expansion, where B specifies a starting base number for the enumeration. For more information and a list of other system variables, see System variables.

See Example: expand="1" below for additional detail.

sample

Accepts an integer value of 1 or 0 that specifies whether or not to generalize the selection based on the value of the value attribute.

When sample="1", a row will appear in the post-selection result with a probability equal to the value of the value attribute. The value attribute can be an expression (or number) that evaluates at each row to a float between 0 and 1 or may also be N/A, which is equivalent to 0.

For instance, <sel sample="1" value="0.1"/> should select approximately 10% of the rows uniformly across the entire table.

The default is 0.

Note: The optional attribute seed can be specified to change the random seed for the sample selection.

(Available as of version 10.17)

seed

Accepts a decimal number that specifies the random seed for the sample selection.

By default, the seed is based on the query hash so that the identical query will deterministically create the same sample.

If seed="0", a seed is non-deterministically generated when the query is run. Because of caching, the sample won't change for the current session unless the cache is cleared, but runs of the same query with seed="0" in different sessions will in general take a different sample.

(Available as of version 10.17)