Syntax Application and Structure

When creating calculated fields, you need to apply the following syntax formatting rules:

  • The queried field, and the table it is in, must be provided inside a pair of square brackets in the format [table].[field], e.g. [Individual].[Age].

    Tip: field names turn blue when recognized.

  • The operator, e.g. equals, must be entered after a space. You can use text, e.g. EQ, or symbols, e.g. =.

  • The value or string being tested must be entered after a space and enclosed within single quotes, e.g. '39'.

  • Multiple criteria can be joined together to form a complex query using the AND or OR query join functions. These functions must be separated from the query by a space, e.g. to find all 39-year-old customers earning <20,000, you might construct an expression like:

     [Individual].[Age] EQ '39' AND [Individual].[Income Bands] EQ '<20000'

Tip: use wildcards when you wish to search for partial strings, e.g. all postal codes starting with BH.

Note: dates in Audiences need to be formatted as either YYYY-MM-DD or MM/DD/YYYY. Entering a date as 01-02-2023 would be read as Jan-2 not 1-Feb.

Operators

Use the following operators to define how your query behaves, e.g. find data equal to a given value.

Operator Text  Symbol Usage
 Equals  EQ  = Locate records that exactly match the given value, e.g. find all individuals with the surname Washington
 Not Equal  NE  <>  Locate records that do not match the given value, e.g. find all individuals who do not have a phone number on record by searching for all entries where the phone number field is not equal to a blank field.
 Less Than  LT  <   Locate numeric or date records with a value less than the given value, e.g. find all orders before 4-July-1776
 Less Than or Equals  LE  <=  Locate numeric or date records with a value less than or equal to the given value, e.g. find all orders on or before 1-July-1997
 Greater Than  GT  > Locate numeric or date records with a value more than the given value, e.g. find all orders with a value over 500 dollars
 Greater than or Equals  GE  >= Locate numeric or date records with a value equal to or more than the given value, e.g. find all customers aged 21 and over
 Like  LIKE   Used in place of equals when using wildcard queries
 Not Like  NOT LIKE   Used in place of not equal when using wildcard queries
 In  IN   Used when testing for multiple values within a single field
 Not In  NOT IN   Used when testing for multiple values not present within a single field
LikeIn LIKEIN   Used in place of In when using wildcard queries

Mathematical Operators

You can use the following mathematical operators in in expressions with numerical values:

  • Addition (+)

  • Subtraction (-)

  • Multiplication (*)

  • Division (/)

Note: When using division (/), make sure the denominator is not 0. If the denominator is 0, the system will return an error. To avoid this, use an IF statement to handle zero values. For example:

IF([CONTACTS].[Total Sends] = 0, 1, [CONTACTS].[Total Sends]

This ensures the denominator is never 0 and prevents errors.

Wildcard queries

You can use the following wildcard characters with text, numeric, geographic and date fields to match patterns in field values.

Underscore

The underscore _ matches a single character. For example:

[Individual].[FirstName] LIKE 'Ro_'

would return Rob but not Robert.

Percent

The percent sign % matches any sequence of zero or more characters. For example:

[Individual].[FirstName] LIKE 'Jo%'

would return Jo, Joe, and Joseph.

[Individual].[FirstName] LIKE '%ina'

would return Ekatarina, Regina, and Mina.

Query validation

It is good practice to validate queries before use to ensure that they are delivering the correct results. Some advice for validation includes:

  • Open the query as a selection and drag all fields on top of the display to preview the data and confirm it looks as expected.

  • Ask a colleague to verify the syntax and results, check operators and counts.

  • Consider the original objective, what are you counting, are the fields used being calculated at the same table level, is the display table correct.

  • Think about blank records, empty values or Nulls. Exclusion queries using NOT syntax will include everything that does not match the criteria defined, so always consider what else the fields could contain.

Testing the query

Once a query has been entered into the editor, it can be tested by selecting the refresh icon on the hamburger menu.

When using the query lozenge to build up query syntax, it is possible to check the syntax and test the count using the test button.