> For the complete documentation index, see [llms.txt](https://docs.redivis.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.redivis.com/reference/workflows/transforms/step-filter.md). # Step: Filter ## Overview The **Filter** step selects rows that meet a certain set of conditions. #### Example starting data: ``` /*---------+--------*  | student | score |  +---------+--------+  | jane    | 83 |  | neal    | 35 |  | sam | 74 |  | pat | 62 |  *---------+--------*/ ``` #### Example output data: Filter out rows with scores less than 70. ``` /*---------+--------*  | student | score |  +---------+--------+  | jane    | 83 |  | sam | 74 |  *---------+--------*/ ``` ## Step structure: Basic state
* A filter step will be made up of one or more filter blocks with a completed condition. * When there are multiple filter blocks in a step, conditions in all blocks must be met for rows to be kept. * If you have a more complex filter statement that is dependent on multiple nested conditions you can select `+` to expand the filter block. Expanded state
* When multiple conditions are needed in a block, you must specify how they relate to each other (`AND` vs `OR`). * Any nested conditions are resolved before higher level conditions. ## Field descriptions
Variables

The variable whose values will be evaluated.

Depending on the operator selected, it may contain multiple variables. Use wildcard characters, such as *, for bulk selection.

[Operator]How the two sides of the condition will be evaluated.
Value(s) or Variable(s)The variable or value which the previously chosen Variables will be evaluated against.

Depending on the operator selected, it may contain multiple variables, values, or a parameter. Use wildcard characters, such as *, for bulk selection.
## Examples ### Example 1: Basic filter Lets say we only want to reduce our table to only contain information about results from the final test. #### Starting data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | quiz    | 83    | jane    | 2020-04-01 |  | quiz    | 35    | pat     | 2020-04-01 |  | quiz    | 89    | sam     | 2020-04-01 |  | midterm | 74   | jane    | 2020-05-01 |  | midterm | 62    | pat    | 2020-05-01 |  | midterm | 93    | sam     | 2020-05-01 |  | final   | 77    | jane    | 2020-06-01 |  | final   | 59   | pat     | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` #### Input fields:
* **Variables:** The variable `test` is where the `final` value we want to evaluate on is located. * **\[Operator]:** We want fields that match exactly, so we choose `=`. * **Values or variables:** We want to only keep rows where `final` is present, so we put that here. #### Output data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | final   | 77    | jane    | 2020-06-01 |  | final   | 59   | pat     | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` ### Example 2: Multiple conditions Let's say we don't just want values from the final but, only those from the final with a score above 60. #### Starting data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | quiz    | 83    | jane    | 2020-04-01 |  | quiz    | 35    | pat     | 2020-04-01 |  | quiz    | 89    | sam     | 2020-04-01 |  | midterm | 74   | jane    | 2020-05-01 |  | midterm | 62    | pat    | 2020-05-01 |  | midterm | 93    | sam     | 2020-05-01 |  | final   | 77    | jane    | 2020-06-01 |  | final   | 59   | pat     | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` #### Input fields:
* We input the data as in the above example, but since we now have two conditions, we have to decided how they relate to each other. In this case we want data that meets all the conditions so select `All conditions must be satisfied (AND)`. #### Output data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | final   | 77    | jane    | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` ### Example 3: Nested conditions Let's say we want to keep all data from the final greater than 60, **or** any any scores above 85. #### Starting data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | quiz    | 83    | jane    | 2020-04-01 |  | quiz    | 35    | pat     | 2020-04-01 |  | quiz    | 89    | sam     | 2020-04-01 |  | midterm | 74   | jane    | 2020-05-01 |  | midterm | 62    | pat    | 2020-05-01 |  | midterm | 93    | sam     | 2020-05-01 |  | final   | 77    | jane    | 2020-06-01 |  | final   | 59   | pat     | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` #### Input fields:
* We want to keep all rows where scores are over 85, or where the test is `final` and the score is over 60. So we set up the `final` and over-60 conditions under an `AND` group, and nest that group under the top-level `OR` condition, alongside the condition for scores over 85. * When executed, the nested condition (test is `final` and score is over 60) is evaluated first, resolving to true or false. Then the higher-level condition, score over 85, OR (test is `final` and score over 60), is evaluated. Any rows that meet this higher-level condition are kept in the output. Any that don't are discarded. #### Output data: ``` /*---------+-------+---------+------------*  | test | score | student | date |  +---------+-------+---------+------------+  | quiz    | 89    | sam     | 2020-04-01 |  | midterm | 93    | sam     | 2020-05-01 |  | final   | 77    | jane    | 2020-06-01 |  | final   | 92    | sam     | 2020-06-01 |  *---------+-------+---------+------------*/ ``` ### Example 4: Comparing variables and value lists Let's say we only want to keep rows where scores are greater than the average score and for selected students. Right now our selected students are Jane and Pat but we know that might change in the future. #### Starting data: ``` /*---------+-------+---------+------------+---------------*  | test | score | student | date | score_average |  +---------+-------+---------+------------+---------------+  | quiz    | 83    | jane    | 2020-04-01 | 69 |  | quiz    | 35    | pat     | 2020-04-01 | 69 |  | quiz    | 89    | sam     | 2020-04-01 | 69 |  | midterm | 74   | jane    | 2020-05-01 | 76.3333 |  | midterm | 62    | pat    | 2020-05-01 | 76.3333 |  | midterm | 93    | sam     | 2020-05-01 | 76.3333 |  | final   | 77    | jane    | 2020-06-01 | 76 |  | final   | 59   | pat     | 2020-06-01 | 76 |  | final   | 92    | sam     | 2020-06-01 | 76 |  *---------+-------+---------+------------+---------------*/ ``` #### Input fields:
* First, create a value list called `selected_students` with the values `jane` and `pat`. Then select it as the comparison for `student`. * With multiple inputs on either side of a condition, the condition is evaluated as an OR. So this condition evaluates to true if the value in `student` equals any value on the list (`jane` or `pat`). * The same result could be achieved by entering `jane` and `pat` directly into the right side of this condition, but using a list makes it easy to change the selected students centrally later, updating every place the list is used. * For the second condition, compare two values from the same row against each other. * When executed, this condition checks whether the value in `score` is greater than the value in `score_average` for each row. If so, the condition is met. #### Output data: ``` /*---------+-------+---------+------------+---------------*  | test | score | student | date | score_average |  +---------+-------+---------+------------+---------------+  | quiz    | 83    | jane    | 2020-04-01 | 69 |  | midterm | 74   | jane    | 2020-05-01 | 76.3333 |  | final   | 77    | jane    | 2020-06-01 | 76 |  *---------+-------+---------+------------+---------------*/ ``` ## Reference: Comparison statements A comparison always evaluates to either TRUE or FALSE. Comparisons are made up of one or more comparison rows. A comparison row always evaluates to TRUE or FALSE. Multiple rows can be nested with a logical AND/OR. A comparison row is made up of three components: ### 1. Left expression The left expression can contain variable parameters from the source table, joined tables, and any newly created variables. All referenced new variables must be "upstream" from the current comparison, with the exception of joins. Joins may reference new variables that are constructed by any variables upstream of that join. Depending on the operator selected, the left expression may contain multiple variables. In this case, each left expression will be evaluated against the right expressions, and logically joined via an OR. If you want to take the logical AND of multiple variables, create a separate comparison row for each variable. Multiple left hand values are only supported for `=` and `like` operators. {% hint style="info" %} Where multiple variables can be entered in a comparison, you can use `*` in the interface to select all matching results. For example, typing DIAG\* and pressing enter will add all variables beginning with "DIAG" to this field. {% endhint %} ### 2. Operator Redivis supports the following operators: #### =, != Checks if any the values in the left expression are equal or not equal to any values in the right expression. `NULL`s are treated as equivalent (`NULL == NULL -> TRUE` and `NULL != NULL -> FALSE`) #### >, >=, <, <=, Checks if the value in the left expression is less than, greater than, etc. the right expression. `String` comparisons are lexicographically ordered, other data types are based on the numeric / temporal order of that value. Comparisons between `NULL` values will always be false. #### like / ! like Checks if the `strings` in the left hand expression matche the specified patterns in the right hand expression. The pattern may contain the following characters: * A percent sign `%` matches any number of characters * An underscore `_` matches a single character * You can escape `\`, `_`, or `%` using a single backslash. For example, `\%`. ### Right expression The right expression can contain any variables allowed in the left expression, as well as literal values and lists. The comparison row will evaluate to TRUE when the left hand expression matches *any* of the right hand expressions, except for the `!=` and `!like` operators, where the comparison will evaluate to true if *all* values are not equal to or not like the left expression. To match against a null datum (empty cell), you must specify the special literal value `NULL` here. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## 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, and the optional `goal` query parameter: ``` GET https://docs.redivis.com/reference/workflows/transforms/step-filter.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. 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.