The Filter rows step keeps rows that satisfy a set of rules. You can use this step to create simple filters with one rule, or complex filters with many rules.
The input data we'll connect to this step has 15 rows of webinar registrants.
Let's say we want to filter this for people who registered after September 1, 2020. By setting a rule to include rows where the value in "Registration Time" column is greater than or equal to "2020-09-01 0:00 AM", we immediately get two rows of data showing who registered after September 1, 2020.
By default, this step will keep rows only if they follow any of the rules. This means that if a row satisfies one of the rules, that row will be kept. You can change this to keep rows only if they match all of the rules by selecting +add rule and changing OR to AND between them.
Now it's time to set up filters for rows. For each rule, you'll select a column, a filtering condition, and a matching value.
Available filtering conditions:
- are blank
- are not blank
- do not equal
- do not contain
- are greater than
- are greater than or equal to
- are less than
- are less than or equal to
To add another rule, click on the blue + add rule button to configure another rule.
- Keeping rows between two values: If you need to only keep rows in a column whose values are between two values, then you'd use two separate rules and combine them by changing OR to AND. Let's say I want to keep values in a column that are between 5 and 10, including 5 and 10. I would set one rule to keep all rows that are greater than or equal to 5. I would set a second rule and change OR to AND between these two rules in order to keep all rows that are less than or equal to 10.
- Filtering dates: As you can see from the example above, the Filter rows step can generally handle filtering for dates that match a certain criteria without any additional date formatting required. However, if you're experiencing any issues with filtering dates, we recommend trying to convert your dates into one of the following formats: Unix format or Lexicographical format. To update your date formats, use a Format dates step before sending your data to the Filter rows step. For Unix format, use capital X for precision to the second and lowercase x for precision to the millisecond. For Lexicographical format, use YYYY-MM-DD HH:mm:ss.
- Accounting for text casing: Please note that rules are not case sensitive.
- Reversing the filtering logic: If you set up a filter with multiple rules and wish to instead see the rows that are being removed by the filter, you can reverse your filtering by changing Keep rows where to Remove rows where.
- Filtering symmetrically: If you need to split your table to create separate branches that will eventually be merged back together to recreate the complete data set, you will use a few Filter rows step while making sure you're not duplicating or losing any rows in that process. For example, let's say you have data that spans three months (October, November, and December) and you need to process each month differently. You want one Filter rows step per month. You'll need to use three separate Filter rows step with the following rules:
- Date is less than 2019-11-01.
- Date is greater than or equal to 2019-11-01 AND less than 2019-12-01.
- Date is greater than or equal to 2019-12-01.
- A good way to make sure you successfully filtered symmetrically is to use a Stack tables step at the end of your separate branches to merge the data back together. The resulting row count should match the row count of your starting data before the Filter rows steps.