Defining filter criteria

You can create ad hoc filters when viewing data, or save filter definitions for use at a later time. Saved filters can be applied on the tables and also used in Scenarios.

Filter operators

By default, when you enter a value in a table column heading, the operator is assumed to be = (equal to) for numeric values and LIKE for text values. However, you can use the operators as described in the following table to evaluate values.

Operator Applies to Example Defines
= Text and Numbers = 5 Equal to 5
= Text and Numbers ="" Returns records with NULL values for the field.
<> Text and Numbers <> 5

Not equal to 5

Note: This operator cannot be used with the wildcard characters (* and %)
<> Text and Numbers <>”” Returns non-null values
< Text and Numbers < 5 Less than 5
> Text and Numbers > 10 Greater than 10
<= Text and Numbers <=5 Less than or equal to 5
>= Text and Numbers >= 10 Greater than or equal to 10
LIKE Text LIKE CZ_099 Include records that start with CZ_099
NOTLIKE Text NOTLIKE CZ_099 Exclude records that start with CZ_099
“
(double quote character)		 Text “ Include records that have a value for this field. Returns non-blank values.
=”
(double quote character)		 Text and Numbers =” Include records that have a blank value (no value) for this field.

For ranges of values, such as numbers between two values, use a format such as:

>3 AND <9

You can also select multiple ranges, such as numbers less than one value or greater than another, such as:

<4 OR >7

Filtering date values

You can filter fields that are populated with date and time values, but keep in mind that the time component has an impact on the filter. For example, suppose you are filtering the Arrive Date Time in the Stops output table, and you enter criteria such as:

> 10/1/2012

This filter criteria will return records on 10/1/2012 that are later than 10/1/2012 12:00:00 AM.

If you want to limit the filter output to all records for a specific date, you can create filter criteria such as:

>= 10/1/2012 and < 10/2/2012

This is interpreted as greater than or equal to 10/1/2012 at 12:00:00 AM, and less than 10/2/2012 at 12:00:00 AM.

In Modeler, when filtering for dates, the filter criteria is always in Short Date/Short Time format regardless of the format selected in Model Options > Data Formats.

Wildcards

You can use wildcards before or after a string value or a number. Both % and * are recognized as wildcards representing multiple characters. The question mark (?) is recognized as a wildcard representing a single character.

Example Defines
DC*
DC%		 Any value beginning with the characters DC.
*DC*
%DC%		 Any value containing the characters DC.
*DC
%DC		 Any value ending in the characters DC.
?23 Any value with one character followed by 23, such as A23, but not AB23.
CZ0? Any value with CZ0 followed by a single character, such as CZ01, but not CZ012.

For example when using * or %, assume you have sites with City values including:

  • Clearwater
  • North Waterford
  • Waterbury
  • Waterville

If you create a filter for City, your results vary based on your use of the wildcard:

  • water* displays Waterbury and Waterville.
  • *water* displays all four values.
  • *water displays Clearwater.

Logical operators

Logical operators enable you to group multiple filters or criteria.

Operator Examples Defines
OR DC* or CZ* Select everything that begins with DC or CZ.
<15 OR >32 Select values that are either less than 15 or greater than 32.
AND NOTLIKE DC* AND NOTLIKE CZ* Select everything that does not begin with DC and does not begin with CZ.
>5 AND <=8 Select values that are greater than 5 and less than or equal to 8, such as 6,7,8.

When naming items such as Scenarios or Customers, keep in mind that the words "and" and "or" are reserved words for evaluating filter criteria. Coupa recommends that you do not include these words in your item names to prevent issues when filtering for these items. If the item value includes "and" or "or", you must preface the name with =" in the filter. For example, assume you created a scenario called "Consider RDC and Alternate Mode". Selecting this value in a filter may not return values in the output table unless you preface it with =". You can alleviate the issue by renaming the scenario to "Consider RDC with Alternate Mode".

Using quotes and equal signs in filter criteria

Typically, you can define filter criteria without the need for quotes or an equal sign. There are some cases where this formatting is required to obtain the desired results. The following table includes examples of when to use the equal sign and quotes in your filter criteria and when it is not a rquirement.

Column value(s) Criteria definition Notes
Consider RDC and Alternate Mode "Consider RDC and Alternate Mode" This value includes the word "and" which is a filter operator. The value must be enclosed in quotes.

Consider RDC

Consider RDC FY2023

 =Consider RDC If you want only the first value, use the equal sign (=) to limit the results to this specific value.

CZ_Saint Paul

CZ_San Jose

 CZ_Saint Paul OR CZ_San Jose

Quotes are typically not required when the value includes embedded spaces.

Note: filtering in Model Building recipes does require quotes when values include embedded spaces.

Last modified: Wednesday March 05, 2025

Is this useful?