Filter types in DESelect Segment

When creating Filters in DESelect Segment, a wide array of filtering options are available. Below you can find an overview of the available Filters.

Filters compared with a fixed value

1. Equals / Not equal to: Matches for the field that is equal to a given value.
   Available for field types: Text, Number, Date, Boolean, EmailAddress, Phone, Decimal, Locale
   Example: Order date equals to "x" months ago.
   
   
2. In / Not in: Compare a field value with a list of values at once. Separate the values with a comma.
   Available for field types: Text, Number, Decimal, EmailAddress, Phone, Locale
   
   
3. Contains: Matches if the given string is a substring of a field value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: when filtering for Name contains ‘John’, the following values would be a match: ‘Johny’, ‘Johnathan’.
   
   
4. Does not contain: Matches if the given string is not a substring of a field value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: When filtering for Name does not contain ‘John’, the following values would be a match: ‘Elisabeth’, ‘Tim’. ‘Johny’ would not be a match.
   
   
5. Contains one of: Matches if any of the given strings is a substring of a field value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: When filtering for emails that contain one of ‘gmail’ or ‘yahoo’, the following values would be a match: ‘x@gmail.com’, x@gmail.es, ‘x@yahoo.co.uk’, ‘x@yahoo.fr’, ...
   
   
6. Does not Contains one of:  Matches if any of the given string is not a substring of a field value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: When filtering for emails that do not contain one of ‘gmail’ or ‘yahoo’, the following values would be a match: ‘x@deselect.com’, ‘x@hotmail.com’. ‘x@gmail.com’ would not be a match.
    
7. Begins with: Matches for records with a field starting with the given value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: When searching for a name that begins with M, ‘Maria’, ‘Melinda’ would be matched, ‘Johny’ would not.
   
   
8. Ends with: Matches for records with a field ending with the given value.
   Available for field types: Text, EmailAddress, Phone, Locale
   Example: When searching for a name that ends with m, ‘Tom’ would be a match, ‘Linda’ would not.
   
   
9. Is empty / Is not empty: Matches for field value being/not being NULL.
   Available for field types: Text, Number, Date, Boolean, EmailAddress, Phone, Decimal, Locale
   
   
10. In Results / Not in Results: checks if a field value matches/does not match the (filtered) values in a column in another data extension.
    In native SQL, you would achieve the same result by writing a subquery.
    Available for field types: Text, Number, Phone, Date, Decimal, Locale, Boolean, EmailAddress
    
    
11. Smaller than: matches if the field value is smaller than a given value.
    Available for field types: Number, Decimal
    
    
12. Smaller than or equal to: matches if field value is smaller than or equal to a given value.
    Available for field types: Number, Decimal
    
    
13. Greater than: matches if the field value is greater than a given value.
    Available for field types: Number, Decimal
    
    
14. Greater than or equal to: matches if the field value is greater than or equal to a given value.
    Available for field types: Number, Decimal
    
    

15. Between: matches if the field value is between the 2 given values (both values included).
    Available for field types: Number, Decimal, Date

    

16. Not between: matches if the field value is not between the 2 given values (both values are excluded).
    Available for field types: Date

    

17. Before: matches if the field value is before the given value (being the given value not included).
    Available for field type: Date

    

    

18. After: matches if the field value is after the given value (being the given value not included).
    Available for field type: Date
    

19. In Previous: matches if the field value is between the given value and the date of execution (being the given value included).
    Available for field type: Date

    

20. In Next: matches if the field value is between the date of execution and the given value (being the given value included).

Dynamic Date Filters

For date fields, there are 2 two options: Calendar and Relative. 

• Option Calendar allows you to select an exact date to match with a field value.
• Option Relative allows you to set a dynamic date indication.
  Example of Relative date filter: 14 days before now or 2 weeks before now. 
  Note: The exact date that applies will be calculated dynamically when the query executes.

Available interval types: Days, Weekdays, Weeks, Months, Quarters, Years, Hours, Minutes

Filter with reference to another field

With this feature, you can compare one field with another field.

Examples:

• Sales Price is bigger than or equal to List Price
• Contract End Date smaller than Contract Start Date (to detect incorrect data)
• Account Id of Order equals Id of Account

Next Step: How to set and populate your target definition (target data extension)?

Related Article: Can I directly convert a date field into a different timezone in the Filters section?