Filter
Keep rows that meet a condition, such as scores above a threshold, or separate matching and nonmatching rows to handle them differently. True has the matches, and False has everything else.
Filter scores
Manual Input has four people with name and score columns. Filter starts in Basic mode with score at least 10. Its True output contains Ada and Sam.
Filter interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| name | score |
|---|---|
| Ada | 10 |
| Sam | 15 |
- Select the False output port. Lin has a score below 10, and Noor has a missing score, so both appear here.
- Change Operator to
greater than, then select the True output port. Only Sam remains: Ada's score equals 10 and no longer passes. - Change Operator to
is null. True now contains only Noor, and no comparison value is needed. - Change Mode to
Custom expressionand enterscore >= 10 AND name = 'Ada'in Expression. True contains only Ada because both conditions must hold; False contains Lin, Sam and Noor.
When to use
Use Filter to keep rows that meet a condition, or to send matching and other rows down different paths. To remove columns, use Select. To remove duplicates, use Unique. To take a fixed number of rows, use Sample.
Read the output
With score at least 10, True contains Ada with 10 and Sam with 15. False contains Lin with 7 and Noor with NULL. Both outputs keep the original name and score columns and their values.
The threshold includes 10. Changing to greater than excludes that boundary, moving Ada to False. Filter changes which output gets a row. It doesn't change the row itself.
Combine conditions
Choose Custom expression when one comparison isn't enough. score >= 10 AND name = 'Ada' matches only Ada in this example. A condition must evaluate to true to reach True; false and NULL both go to False.
See the functions reference for functions you can use in expressions.
To choose columns instead of rows, use Select.
To take a fixed number of rows, use Sample.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
2 ports- True
- False
Configure the block
Build one comparison in Basic mode, or combine conditions in a custom expression.
- Mode
Basic uses the comparison controls. Custom expression accepts a SQL condition over the input columns. Default: Basic.
- Column
Choose the column to test in Basic mode.
- Operator
Choose a comparison, a null or empty test, a text contains test, a boolean test or a range. At least, at most, from, up to and in range include their boundaries.
- Value
Enter the comparison value. For in range, this is the lower bound. Null, empty and boolean tests don't need a value.
- Upper Value
For in range, enter the inclusive upper bound.
- Expression
For Custom expression, enter a DuckDB SQL condition over the input columns. Use single quotes for text values. Example: amount > 100 AND status = 'active'. Matching rows go to True and the rest to False. Window functions such as row_number() aren't allowed here; compute them in Formula first.
Filter FAQs
- What happens to missing scores?
- A comparison such as score >= 10 evaluates to NULL for a missing score, so that row goes to False. Use is null to send missing values to True instead.
- Are rows discarded?
- Every input row goes to exactly one output. Connect True to continue with matching rows, False for the remaining rows or both for separate branches.
- What is the difference between is null and is empty?
- Is null matches missing values only. Is empty matches both NULL and empty strings, but not strings containing only spaces.
- Is contains case-sensitive?
- Yes. The Basic contains test searches for the exact text, including its letter case.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.