> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Common expression patterns
> Build conditional logic, filters, and reusable expressions with the visual expression builder
Use the visual expression builder to create conditional logic, combine multiple filter conditions, build reusable expressions with parameters, and define complex business rules without writing SQL manually.
Common use cases include:
* categorizing records based on conditions
* building multi-condition filters with `AND` and `OR`
* creating derived columns
* handling null values
* using parameters in expressions
* creating reusable business logic across pipelines
This page explains common expression patterns you can create with the visual expression builder.
## Common expression examples
| Goal | Example pattern |
| :---------------------------------- | :------------------------------------------ |
| Categorize records using conditions | `WHEN revenue < 1000000 THEN 'Low Revenue'` |
| Combine multiple conditions | `Amount > 100000 AND Region = 'APAC'` |
| Replace null values | `COALESCE(region, 'Unknown')` |
| Build nested logic | Multiple `WHEN` clauses with `ELSE` |
| Filter rows using grouped logic | Nested `AND` / `OR` conditions |
| Use runtime values in expressions | Configuration variables and parameters |
## Expressions that return values vs conditions
Some expressions return calculated values, while others return `true` or `false`.
For example:
* Filter conditions must evaluate to `true` or `false`.
* Reformat expressions can return calculated values such as text, numbers, or dates.
Understanding the expected output type can help prevent validation and runtime errors.
## Expression-building features
The following table describes options for the visual expression builder.
| Feature | Description |
| ---------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Comparison | Lets you establish relationships between two simple expressions connected by an operator. This mode helps you perform comparisons or existence checks on your data that always evaluate to true or false. |
| Grouping | Combine multiple comparison expressions into groups using logical operators like `AND` and `OR`. This structure enables you to express intricate business logic in a visual format. |
| Parameters | Enables you to include variables in your expressions that may vary at runtime. Parameters only show up in the **Configuration Variables** of the visual expression builder after you have [created them](/data-analysis/development/parameters/parameters) at the pipeline level. |
## Examples
### Create conditional logic for derived columns
Let's say you want to stratify accounts based on their annual revenues. Each condition we set up is limited to one comparison. This example combines conditional logic with comparison operators.
#### Create a new conditional column
To set up the comparison expressions to match the image above:
1. In the Reformat gem, under **Target Column**, click **Select Column**.
2. Give the column the name `stratify_by_revenue`.
3. Click **Select expression > Conditional**. A `WHEN` clause appears.
#### Configure the WHEN clause
1. For `WHEN`, click **Select expression > Function**.
2. Select **Data type cast**, which converts a value of one data type into another data type.
3. Select **Throw error on failure** to ensure the pipeline doesn't run if the type cast fails.
4. Click **Select expression > Column** and select `ANNUALREVENUE`.
5. Click **Select data type > Float** to convert the column to a Float type.
6. Click **Select operator** and select `less than`.
7. Click **Select expression > Value** and enter `1000000` as the value.
#### Configure the THEN clause
1. For `THEN`, click **Select expression** and select **Value**. Enter `Low Revenue` as the value.
2. Click `+` on the next line and select **Add CASE** to add another `WHEN` clause.
3. Repeat steps 3 to 8 to set up the rest of the comparison expressions.
4. Click `+` on the next line and select **Add ELSE** to add an `ELSE` statement.
5. Click **Select expression** and select **Value**. Enter `Unknown` as the value.
This conditional expression will categorize your accounts based on revenue thresholds, making it easier to perform segment-specific analysis and reporting. When the pipeline runs, each account will be assigned to the appropriate revenue category based on the conditions you've defined.
### Build multi-condition filters with AND/OR logic
When filtering data, you often want the output data to meet multiple criteria. You can use Grouping for this by creating multiple `AND` and `OR` statements.
Assume you have a dataset where you want to filter for the following:
* Total expected revenue that `is not null`
* Total amounts that are greater than `100000`
* Latest closed quarters that equals `2023Q2` or `2024Q2`
You can have any number of groups and nestings (a group within a group). You can also always
change the grouping conditions between `AND` and `OR`.
#### Set up base filter conditions
To set up the grouping expressions to match the image above:
1. After creating the Filter gem, click **Add condition**. An option to Select expression appears.
2. Click **Select expression > Column**.
3. Select `TOTAL_EXPECTED_REVENUE` from the list.
4. Click **Select operator** and select `is not null`.
5. Click **+ Add Condition** to add another condition expression.
6. Click **Select expression > Column**.
7. Select `TOTAL_AMOUNT` from the list.
8. Click **Select operator** and select `greater than`.
9. Click **Select expression > Value**.
10. Enter `100000` as the value.
#### Add grouped `OR` condition
1. Click **Add Group**. A grouped expression row appears.
2. Click **Select expression > Column**.
3. Select `LATEST_CLOSED_QTR` from the list.
4. Click **Select operator** and select `equals`.
5. Click **Select expression > Value**.
6. Enter `2023Q3` as the value.
7. Click **+ Add Condition** and repeat steps 2 to 6 to set up the other `OR` condition.
This complex filter will return only high-value opportunities from specific quarters that have valid expected revenue values. By combining AND and OR conditions in this way, you can create precise data subsets that match your exact business requirements.
### Create reusable expressions with parameters
When you use a [pipeline parameter](/data-analysis/development/parameters/parameters) in a visual expression, you can manipulate the value of that parameter using different configs at runtime. Let's review an example that leverages an array parameter in a Filter gem.
Imagine that you want to filter an `Orders` dataset based on the region where the order was placed. Specifically, you only want to keep rows where the region is included in the array parameter.
#### Create an array parameter
First, you'll set up a `region` parameter, which will be an array of strings that includes a subset of regions.
1. Open your project and select **Parameters** in the header.
2. Click **+ Add Parameter**.
3. Name the parameter `region`.
4. Select the **Type** and choose **Array > String**.
5. Click **Select expression > Value**.
6. Type `AMER` and click **Done**.
7. Select `+` to add another string to the array.
8. Type `APAC` and click **Done**.
9. Now, click **Save**.
#### Use the parameter in an expression
Now, you'll use the parameter in an expression inside a Filter gem.
1. Create and open the Filter gem.
2. Remove the default `true` expression.
3. Click **Select expression > Function** and select `array_contains`.
4. In the **array** dropdown of the function, click **Configuration Variable** and select the `region` parameter.
5. In the **value** dropdown of the function, click **Column** and select the order region column.
The output of this gem will only include rows where the order region matches at least one value in the `region` array. When you run the pipeline interactively, it will use the values of the default array that you set up in the previous section.
## Validate your expressions
Run the pipeline up to and including the gem with your expression, and observe the resulting data sample. To do so, click the **play** button on either the canvas or the gem. Once the code has finished running, you can verify the results to make sure they match your expectations. You can explore the result of your gem in the [Data Explorer](/data-analysis/development/runs/data-explorer/data-explorer).
## Common issues
### Filter expressions failing validation
Filter conditions must evaluate to `true` or `false`.
For example:
* `Amount > 1000` is valid.
* `CASE WHEN Amount > 1000 THEN 'High' END` is not valid as a filter condition because it returns text instead of a boolean value.
Use conditional expressions to create derived columns in gems like [Reformat](/data-analysis/gems/prepare/reformat), and use boolean comparisons in [Filter](/data-analysis/gems/prepare/filter) gems.
### Conditional logic not returning expected results
Verify that:
* conditions are evaluated in the expected order.
* grouped `AND` and `OR` conditions are structured correctly.
* all possible cases are handled with an `ELSE` condition when appropriate.
### Null values causing unexpected behavior
Some expressions return `NULL` when input values are `NULL`.
Use functions such as `COALESCE()` to provide default values when needed.
### Expression validation errors
Validation errors can occur when:
* data types do not match.
* required function arguments are missing.
* column references are incorrect.
* expressions return an unexpected value type.
### Nested field references not resolving correctly
When working with nested or structured data, verify that:
* the correct field path is selected.
* the referenced field exists in the input schema.
* the expression uses the expected nested structure.
### Multi-condition filters returning unexpected rows
When combining `AND` and `OR` conditions:
* use grouping to control evaluation order.
* verify that conditions are nested correctly.
* test expressions incrementally to confirm the output.
## Tips
Here are some additional tips to keep in mind when using the visual expression builder:
* The expression dropdowns support search.
* Each argument of your function is another expression since you have the same expression options to choose from.
* You can drag and drop your comparison expressions to rearrange them.
* Just as with conditions, you can also drag and drop your grouping expressions to rearrange them.
* You can delete individual expressions, conditions, and groupings by clicking the trash icon at the end of the rows.