Filter operators for text, number and date columns
In a Conditional Filter in Builder mode, the column's type decides which operators you can pick. On a Date column you also choose a time interval, which lets you filter by month, weekday, year or a rolling window. For the filter steps, see Filter rows.
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 on each run. 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.
Related
- Filter rows: build, combine and apply conditions.
- Work with dates and financial years: shift or extract dates before filtering.
- Remove duplicates and check data: quality gates on pipeline output.
Something wrong? support@mammoth.io