Overview

The where statement is used to define a local filter, which restricts the scope of any statements contained within its body.

Syntax

where (conditional expression) {
   <statements>
}

Details

The where statement is used to define a local filter which restricts the effect of the statements located in the lines between the opening { and closing }.

These braces define the start and end of the body of the where statement.

• The opening { must be preceded with white-space may be positioned on the same line as the where statement or may be on a line of its own

• The closing } must be located on a line of its own

Only statements that support a local filter may be located with the body. If a non-supported statement is included in the body then a log entry is generated and the task will fail.

Only rows in the DSET associated with the expression for which the conditional expression is true will be affected by the statements in the body of the where statement.

Referencing column names

As the purpose of the where statement is to selectively process rows of data based on the expression, it is a requirement that the condition references one or more column names.

To reference a column in an expression, its name should be specified, enclosed in square brackets [like_this].

To illustrate this in use, consider a DSET called test.data which contains the following:

user key,value,name
d,4,dexter
f,6,freddy
g,7,geoff
a,1,andy

The following script will create a new column called "flag" and set that new column value to 1 for all rows where the user key is f.

# Create a blank column called 'flag'
create column flag
​
# Set values in 'flag' to 1 where the user key is f
where (["user key"] == f) {
   set flag to 1
}

A blank value in a column can be checked for using a pair of quotes with no content - "". The following will delete all rows in the DSET Azure.usage where the quantity column is empty:

where ([Azure.usage.quantity] == "") {
    delete rows
}

Regular expressions

Regular expressions are often used to restrict processing to certain rows in a dataset.

For example:

# Delete all rows where the hostname contains the word 'test'
where ([hostname] =~ /.*test.*/) {
    delete row
}

As is the case with the if statement, the entire value in the column must match the expression in order for the row to be processed (there is no implicit .* at the end of an expression).

Supported statements

The following statements may be used in the body of a where statement:

• ​calculate​

• ​convert​

• ​copy​

• ​create mergedcolumn​

• ​delete rows​

• ​move​

• ​replace​

• ​round​

• ​set​

• ​split​

• ​timerender​

The following statements may also be used in the body of a where statement but are unaffected by the expression (and as a result work exactly the same as if they were used outside the where statement body)

• ​default​

• ​export​

• ​import​

• ​option​

• terminate

• ​var​

Examples

# Delete all rows where the VMID is 1234
​
where ([VMID] == 1234) {
    delete rows
}