> AI agents: this is one page from Mammoth Analytics documentation. The index of all pages as Markdown is https://mammoth.io/docs/llms.txt. Append `.md` to any docs URL, or send `Accept: text/markdown`, to get Markdown.

# Filter rows in Mammoth: keep or remove by condition

> Filter rows in a Mammoth pipeline with Conditional Filter: keep or remove rows by text, number or date rules, AND/OR groups or SQL, or keep the top rows.
>
> Applies to: All plans. Roles: Project Editor or Project Admin. Where: View › Describe your transformation › Filter, Label & Replace › Conditional Filter

**Conditional Filter** keeps or removes rows that match one or more conditions. Open it from **Describe your transformation** (or a **+** in the Pipeline panel) › **Filter, Label & Replace** › **Conditional Filter**; it is also a shortcut under **AI Powered**. The same **Filter** group has **Remove Duplicate Rows** and **Show Top/Bottom Rows**. Put filters early in the pipeline to cut the rows later tasks must process.


## Describe a filter in plain English

1. Open **Conditional Filter**.
2. Click the prompt bar at the top (**Describe what you want to generate** in SQL mode).
3. Type what you want, for example *"Show entries where hotel class is 5-Star and region is Europe"*.
4. Click **Generate**. Mammoth builds the condition in whichever mode is active.
5. Check the result, then click **Apply**.

Use the revert button beside **Generate** to go back to the previous result.

A saved filter reopens in the mode it was built in.


## Build a filter condition without SQL

In **Builder** mode you set conditions with dropdowns.

1. At the top, choose **Keep** or **Remove** in front of "values where…". **Keep** leaves only matching rows; **Remove** drops them. The choice applies to the whole task.
2. Pick a **Column**. Its type (Text, Number or Date) decides which operators you get.
3. Pick an **Operator** (see the tables below).
4. Click the comparison chip in front of the input (**Val**, **Col** or **#**) to compare against a value, another column or a parameter.
5. Enter the value, pick the second column, or select the parameter.
6. Read the line below the builder, which restates the condition in words, for example **Keep** WHERE Outlet is **Restaurante**.
7. Click **Apply**. In Draft mode the main button reads **Add to Pipeline** and stages the task without running it; the arrow beside it offers the other choice.

## Compare with a value, a column or a parameter

Click the chip in front of the input (**Val**, **Col** or **#**) to choose what the column is compared with.

| Comparison type | Use it to |
| --- | --- |
| **Value** | Compare against a fixed value you type. Suggestions come from the column's real data. |
| **Column Value** | Compare against another column in the same row, for example keep rows where `actual_sales` is greater than `target_sales`. |
| **Parameter** | Compare against a project parameter. The picker lists parameters whose type matches the column, and each run uses the parameter's current value. See [Parameters and snippets](https://mammoth.io/docs/learn/organise/parameters-and-snippets/). |

A **Case sensitive** checkbox appears below the conditions once at least one Text condition exists. Unticked (the default), comparisons like **is**, **contains** and **starts with** ignore case. Tick it when exact casing matters.

## Operators for each column type

The column's type decides the operators you can pick.

### Operators for text columns

| Operator | Keeps rows where the value… |
| --- | --- |
| **is** | exactly equals the value, or any one of several values you enter |
| **is not** | doesn't equal the value, or any of several values you enter |
| **contains** | includes the text, or any one of several texts you enter, anywhere |
| **does NOT contain** | doesn't include the text anywhere |
| **starts with** | begins with the text |
| **ends with** | ends with the text |
| **does NOT start with** | doesn't begin with the text |
| **does NOT end with** | doesn't end with the text |
| **is Empty** | is null or blank |
| **is NOT Empty** | has any non-null, non-blank value |

### Operators for number columns

| Operator | Keeps rows where the value… |
| --- | --- |
| **is** | exactly equals the number, or any one of several numbers you enter |
| **is not** | doesn't equal the number, or any of several numbers you enter |
| **is less than** | is strictly less than the number |
| **is less than or equal to** | is less than or equal to the number |
| **is greater than** | is strictly greater than the number |
| **is greater than or equal to** | is greater than or equal to the number |
| **is MAX value** | equals the column's maximum |
| **is NOT MAX value** | doesn't equal the maximum |
| **is MIN value** | equals the column's minimum |
| **is NOT MIN value** | doesn't equal the minimum |
| **is Empty** | is null |
| **is NOT Empty** | has any non-null value |
| **in between (exclusive)** | falls between two numbers, not including the endpoints |

On a Number column marked as an ID, **starts with** and **does NOT start with** are also offered, near the top after **is** and **is not**. They match on the digits, so `starts with 40` keeps 4012 and 40877.

### Operators for date columns

| Operator | Keeps rows where the date… |
| --- | --- |
| **is** | equals the date |
| **is NOT** | doesn't equal the date |
| **is later than** | is after the date |
| **is on or later than** | is on or after the date |
| **is earlier than** | is before the date |
| **is on or earlier than** | is on or before the date |
| **in between (exclusive)** | falls between two dates, not including the endpoints |
| **is Empty** | is null |
| **is NOT Empty** | has any non-null value |

## Filter dates by month, weekday, year or a rolling window

On a Date column with the **Value** comparison type, a **Select time interval** dropdown appears before the value. It sets how precisely dates are compared and what you enter. **in between (exclusive)** skips it and shows a date-time picker for each end.



| Time interval | You enter | The condition compares |
| --- | --- | --- |
| **Date** | A date | The date, ignoring the time of day |
| **Date - Time in seconds** | A date and time to the second | Date and time, to the second |
| **Date/Minute** | A date and time | Date and time, to the minute |
| **Date/Hour** | A date and hour | Date and time, to the hour |
| **Year/Month** | A year and a month | Year and month only |
| **Year** | A year | Year only |
| **Day of Month** | A day from 1 to 31 | The day of the month in any month and year |
| **Month** | A month name | The month in any year |
| **Weekday** | A weekday | The day of the week |
| **Earliest single value** | Nothing | The earliest date in the column |
| **Latest single value** | Nothing | The latest date in the column |
| **Earliest Date - Time** | A number and a unit (Year to Second) | The earliest date in the column plus that amount |
| **Latest Date - Time** | A number and a unit (Year to Second) | The latest date in the column plus that amount. Enter a negative number to go back. |
| **Current Date (UTC)** | A number and a unit (Year, Month, Week or Day) | The UTC date the step runs, plus that amount |

Earliest, latest and current date options are worked out again each time the pipeline runs.

To keep the last 30 days, choose **is on or later than**, set the interval to **Current Date (UTC)**, and enter `-30` Days. The window moves forward on every run.

## Combine conditions with AND, OR and brackets

1. Click **Condition** to add another condition row.
2. On the second row, choose **And** (both must be true) or **Or** (either is enough) in the dropdown.
3. To build compound logic such as *(A AND B) OR (C AND D)*, click **Group** (available with two or more conditions) to wrap the last condition and a new one in brackets; each group has its own And/Or. The AND/OR inside a group is worked out first, then combined with the outer conditions.

![Conditional Filter builder with the rule hotel_class is 5-Star and a WHERE clause preview](https://mammoth.io/docs/images/20260511_164722_numeric-operators1.webp)

With more than one condition, each row has icons on the right:

| Icon | Action |
| --- | --- |
| Nest | **Place inside parentheses**: move this condition into a nested group |
| Unnest | **Place outside parentheses**: move it out of its group (only inside a group) |
| Copy | **Duplicate** this condition row |
| Trash | **Remove** this condition row |

## Write a filter as a SQL WHERE clause

SQL mode is where a new filter opens. Type any valid SQL WHERE expression in the **SQL Expression** area, using your column names.

![Conditional Filter SQL tab with an expression prompt, a Generate button and an empty SQL Expression editor](https://mammoth.io/docs/images/20260511_164826_sql-tab-view1.webp)

- Pipeline steps run on DuckDB, so write DuckDB SQL. Put column names in double quotes and text in single quotes, for example `"region" = 'Europe' AND "amount" > 100`.
- The **Describe what you want to generate** bar still works: **Generate** fills the **SQL Expression** field.
- **Keep** / **Remove** sits under the **SQL** / **Builder** toggle here too. Write the condition for the rows to match, then pick whether they stay or go.
- SQL mode has no **Insert Parameter** button, and `{Region}` text in a filter's SQL isn't read as a parameter. To compare against a parameter, switch to **Builder** and use the **Parameter** comparison type.

Use SQL mode when the Builder can't express the logic, when you want to paste a ready-made condition, or when you are moving logic from another tool. More on SQL expressions in [Calculate with formulas](https://mammoth.io/docs/learn/clean-and-shape/formulas/).

## Filter from an Explore card

In a column's Explore card, select one or more values and choose **Keep all values like this** or **Remove all values like this** from the card's menu. A Conditional Filter opens in Builder with those values filled in. On a Text column, **Case sensitive** comes ticked. See [Explore cards](https://mammoth.io/docs/learn/explore/explore-cards/).

You can also choose **Apply Filter with this Column** in a column's 3-dot menu to open a filter set to that column.

## Remove duplicate rows

**Remove Duplicate Rows** sits in the same **Filter** group. It drops rows that match on all columns, all columns except some, or only the columns you pick, and keeps the last row of each set. Settings and an example are in [Remove duplicate rows and add data quality checks](https://mammoth.io/docs/learn/clean-and-shape/duplicates-and-checks/).

## Keep only the top or bottom rows

**Show Top/Bottom Rows** keeps a fixed number of rows from the top or bottom of your data.

![Show Top/Bottom Rows panel with 5 rows set to Top and a Sort added summary sorted by order_amount descending](https://mammoth.io/docs/images/20260421_092158_ShowTop_BottomRowsconfigurationpanelshowing1.webp)

1. In **Type amount of rows**, enter how many rows to keep. To use a Number parameter, click the **Val** chip and choose **Parameter**.
2. In **Select rows position**, choose **Top** for the first rows or **Bottom** for the last.
3. Check the **Sort added** chip, if shown. It shows the column the data is sorted by, which decides what "top" means. Sorted by order_amount ascending, Top 10 gives the 10 lowest values. Edit or remove the sort from the chip.
4. Click **Apply**.

## Troubleshooting filters

| Symptom | Cause | Fix |
| --- | --- | --- |
| The preview reads "No rows in the result." | The condition matches nothing (with **Keep**) or everything (with **Remove**) | Check the operator, the value and the **Keep** / **Remove** choice |
| A text filter misses rows that differ only in case | **Case sensitive** is ticked | Untick it |
| A text filter catches rows with different casing | **Case sensitive** is off by default | Tick **Case sensitive** |
| `{Region}` in a SQL filter isn't replaced | Filter SQL doesn't read parameters | Use **Builder** with the **Parameter** comparison type |
| Top 10 returns the lowest values | The sort is ascending | Edit the **Sort added** chip to sort descending |

## Related

- [How the pipeline works](https://mammoth.io/docs/learn/clean-and-shape/how-the-pipeline-works/): preview a filter and see the removed rows before applying.
- [Label rows, edit cells and fill blanks](https://mammoth.io/docs/learn/clean-and-shape/label-and-insert/): use the same conditions to label rows instead of removing them.
- [Remove duplicates and check data](https://mammoth.io/docs/learn/clean-and-shape/duplicates-and-checks/): quality gates on pipeline output.
- [Work with dates and financial years](https://mammoth.io/docs/learn/clean-and-shape/dates/): shift or extract dates before filtering.
- [Parameters and snippets](https://mammoth.io/docs/learn/organise/parameters-and-snippets/): filter on a value you can change in one place.

---
Source: https://mammoth.io/docs/learn/clean-and-shape/filter-rows.md · Updated: 2026-11-08