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

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

The underscore _ matches a single character. For example:

[Individual].[FirstName] LIKE 'Ro_'

would return Rob but not Robert.

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.

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.

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.