# where

## 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

{% hint style="info" %}
The expressions used by the `where` statement take the same form as those used in the [if ](/3.5.4/data-pipelines/transform/language/if.md)statement.

The information below covers aspects of expression use that are unique to the `where` statement
{% endhint %}

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.

{% hint style="warning" %}
The conditional expression associated with the `where` statement **must** reference at least one column name as detailed in the *Column names* section below
{% endhint %}

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]`.

{% hint style="warning" %}
If the column name contains white-space then it should be quoted **within** the brackets `["like this"]`. The reason that the quotes must be located within the brackets is that otherwise the brackets (along with their contents) would be treated as a literal string.
{% endhint %}

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
}
```

{% hint style="danger" %}
It is possible for one or more statements (such as [set](https://docs.exivity.com/~/drafts/-LQYs8SHeGlk8CkWgZRx/primary/diving-deeper/transform/language/set)) within the statement block enclosed by `where { ... }` to modify the contents of a column that is being used by the expression.

Doing this may lead to unexpected results as for each statement in the block the expression is re-evaluated for every row in the data being processed, so if `set` is used to update the values in a column then the new values will be used for all subsequent evaluations of the expression, which will affect all following statements in the `where` block.

If updating a value in any column(s) referenced by the expression, it is recommended to make a copy of the column before the `where` statement, using

`set copy_of_column as original_column`

The values in the copy can then be freely modified, and the original column can be replaced with it after execution of the `where` statement has completed.
{% endhint %}

### 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 ](/3.5.4/data-pipelines/transform/language/if.md)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](/3.5.4/data-pipelines/transform/language/calculate.md)
* [convert](/3.5.4/data-pipelines/transform/language/convert.md)
* [copy](/3.5.4/data-pipelines/transform/language/copy.md)
* [create mergedcolumn](/3.5.4/data-pipelines/transform/language/create.md#merging-column-values-to-create-a-new-column)
* [delete rows](/3.5.4/data-pipelines/transform/language/delete.md#deleting-rows)
* [move](/3.5.4/data-pipelines/transform/language/copy.md)
* [replace](/3.5.4/data-pipelines/transform/language/replace.md)
* [round](/3.5.4/data-pipelines/transform/language/round.md)
* [set](/3.5.4/data-pipelines/transform/language/set.md)
* [split](/3.5.4/data-pipelines/transform/language/split.md)
* [timerender](/3.5.4/data-pipelines/transform/language/timerender.md)

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](/3.5.4/data-pipelines/transform/language/default.md)
* [export](/3.5.4/data-pipelines/transform/language/export.md)
* [import](/3.5.4/data-pipelines/transform/language/import.md)
* [option](/3.5.4/data-pipelines/transform/language/option.md)
* terminate
* [var](/3.5.4/data-pipelines/transform/language/var.md)

## Examples

```
# Delete all rows where the VMID is 1234

where ([VMID] == 1234) {
    delete rows
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://olddocs.exivity.io/3.5.4/data-pipelines/transform/language/where.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.