v7 v8

Define filter conditions

Last update: 2023-07-31

Choose the operator

Within filtering conditions, you need to link two values together using an operator.

Below is a list of the operators available:

Operator
 Purpose
 Example
Equal to
Returns a result identical to the data entered in the second Value column.
 Last name (@lastName) equal to 'Jones', will return only recipients whose last name is Jones.
Greater than
Returns a value greater than the value entered.
 Age (@age) greater than 50, will return all values greater than '50', i.e. '51', '52', etc.
Less than
Returns a value smaller than the value entered.
 Creation date (@created) before 'DaysAgo(100)', will return all recipients created less than 100 days ago.
Greater than or equal to
Returns all values equal to or greater than the value entered.
 Age (@age) greater than or equal to '30', will return all recipients aged 30 or more.
Less than or equal to
Returns all values equal to or lower than the value entered.
 Age (@age) less than or equal to '60', will return all recipients aged 60 or less.
Not equal to
Returns all values not identical to the value entered.
 Language (@language) to equal to 'English'.
Starts with
Returns the results starting with the value entered.
 Account # (@account) starts with '32010'.
Does not start with
Returns the results not starting with the value entered
 Account # (@account) does not start with '20'.
Contains
Returns the results containing at least the value entered.
 Email domain (@domain) contains 'mail', will return all domain names that contain 'mail'. So the 'gmail.com' domain will also be returned.
Does not contain
Returns results not containing the value entered.
 Email domain (@domain) does not contain 'vo'. In this case, domain names which contain 'vo' will not be returned. The 'voila.fr' domain name will not appear in the results.
Like
Like is very similar to the Contains operator. It lets you insert a % wild card character in the value.
 Last name (@lastName) like 'Jon%s'. Here, the wild card character is used as a "joker" to find the name "Jones", should the operator have forgotten the missing letter between the 'n' and the 's'.
Not like
Is similar to Like . Lets you not recover the entered value. Here too, the entered value must contain the % wild card character.
 Last name (@lastName) not like 'Smi%h'. Here, the recipients whose last name is 'Smi%h' will not be returned.
Is empty
In this case, the result we are looking for matches an empty value in the second Value column.
 Mobile (@mobilePhone) is empty returns all recipients who do not have a mobile number.
Is not empty
Works in reverse to the Is empty operator. It is not necessary to enter data in the second Value column.
 Email (@email) is not empty.
Is included in
Returns results included in the values indicated. These values have to be separated by a comma.
 Birth date (@birthDate) is included in '12/10/1979,12/10/1984', will return the recipients born between these dates.
Is not included in
Works like the Is included in operator. Here, we want to exclude recipients based on the values entered.
 Birth date (@birthDate) is not included in '12/10/1979,12/10/1984'. Unlike in the previous example, recipients born within these dates will not be returned.

Use AND, OR, EXCEPT

For queries using several filtering conditions, you need to define links between the conditions. There are three possible links:

  • And lets you combine two filtering conditions,
  • Or lets you offer an alternative,
  • Except lets you define an exception.

Click And (offered by default) and choose from the drop-down list.

  • And: adds a condition and enables overfiltering.

  • Or: adds a condition and enables overfiltering.

    The following example lets you find recipients whose email domain contains “orange.co.uk” OR whose post code starts with “NW”.

  • Except: if you have two filters and the first one does not return a value, this type of link creates an exception.

    In the following example, we want to return recipients whose email domain contains “orange.co.uk” EXCEPT if the recipient’s last name is “Smith”.

This example shows a filter which lets you display: recipients who either speak Spanish, OR are women with mobile numbers, OR recipients without an account number and whose company name starts with the letter “N”.

Prioritize conditions

This section explains how to prioritize conditions thanks to the blue arrows in the toolbar.

  • The arrow pointing to the right lets you add a level of parentheses to the filter.

  • The arrow pointing to the left lets you delete a selected parenthesis level from the filter.

  • The vertical arrows let you move a condition, thereby changing their execution sequence.

This example shows you how to use the arrow to delete a parenthesis level. Start from the following filtering condition: City equal to London OR gender equal to male and mobile not indicated OR account # starts with “95” and company name starts with “A”.

Place your cursor on the Gender (@gender) equal to Male filtering condition and click the Remove a parenthesis level arrow.

The Gender (@gender) equal to Male condition has been taken out of its parenthesis. It has moved to the same level as the “City equal to London” condition. These conditions are linked together (And).

Select data to extract

The available fields vary from one table to another. All fields are stored in a main node known as the Main element. In the following example, the available fields are in the recipient table. Fields are always displayed alphabetically.

The detail the selected field is visible at the bottom of the window. For example, the Email domain field is a Calculated SQL field and its extension is (@domain).

NOTE

Use the Search tool to find an available field.

Double-click an available field to add it to the output columns. At the end of the query, each selected field creates a column in the Data preview window.

Advanced fields are not displayed by default. Click Display advanced fields in the bottom right-hand corner of the available fields to display everything. Click again to return to the former view.

For example, in the recipient table, the advanced fields are Boolean 1, Boolean 2, Boolean 3, Foreign key of “Folder” link, etc.

The following example shows the advanced fields of the recipient table.

The various categories of fields:

Icon
 Description
 Examples
Simple field
 Email, gender, etc.
Primary key. This SQL field is a way of identifying a record in a table.
 Identifier recipients are primary keys and identifiers are unique by definition.
Foreign key. Used as a link to another table.
 Recipient foreign key, service foreign key, etc.
Calculated field. This type of field is calculated on request using the values in the database.
 Age, email domain, etc.
Field containing long texts.
 Comment, full address, etc.
Indexed SQL field.
Full name, ISO code, etc.

Link to a table and collection element:

Icon
 Description
 Example
Links to a table in particular. These coincide with 1-1 type associations. An occurrence of the source table can coincide with only one occurrence of the target table. For example, only one recipient can be linked to a country.
 Folder, State, Country, etc.
Collection element on a specific table. These coincide with 1-N type associations. One source table occurrence can coincide with several occurrences of the target table, but one occurrence of the target table can coincide with only one occurrence of the source table. For example, one recipient can subscribe to 'n' subscription letters.
 Subscriptions, lists, exclusion logs, etc.
NOTE
  • Use the Add button (above the side icon bar) to add an output column in which we wish to edit the expression. For more on editing an expression, refer to this section.
  • Delete an output column by clicking the red ‘x’ (Delete).
  • Change the order of the output columns using the arrows.
  • The Distribution of values serves as a way to view the distribution of the values of the field selected (for example, the distributions linked to recipient towns, recipient languages, etc.).

Create calculated fields

If necessary, add a column during data formatting. A calculated field adds a column to the data preview section. Click Add a calculated field.

There are four types of calculated fields:

  • Fixed string: lets you add a string of characters.

  • String with JavaScript tags: the value of the calculated field combines a string of characters and JavaScript directives.

  • JavaScript expression: the value of the calculated field is the result of a JavaScript function evaluation. The returned value can be typed (number, date, etc.).

  • Enumerations: This type of field lets you use/modify the content of one of the output columns in a new column.

    It’s possible to use the source value of a column and give it a destination value. This destination value will be displayed in the new output column.

    An example of adding calculated field type Enumerations is available, refer to this section.

    The Enumerations type calculated field can include 4 conditions:

    • Keep the source value restores the source value to the target without changing it.
    • Use the following value lets you enter a default destination value for non-defined source values.
    • Generate a warning and continue warns the user that the source value cannot be changed.
    • Generate an error and reject the line prevents the line from being calculated and imported.

Click the Detail of calculated field to view the detail of the inserted field.

To remove this calculated field, click the Remove the calculated field cross.

Build expressions

The expression editing tool lets you calculate aggregates, generate function, or edit a formula using an expression.

The following example shows you how to run a count on a primary key.

Apply the following steps:

  1. Click Add in the Data to extract window. In the Formula type window, select a type of formula to enter the expression.

    There are several types of formulas available: Field only, Aggregate, Expression.

    Select Process on an aggregate function, and Count. Click Next.

  2. The primary key is calculated.

Here is a detailed view of the choices available in the Formula types window:

  1. Field only lets you return to the Field to select window.

  2. Aggregate (Process on an aggregate function). Here are some examples of aggregate use:

    • Count lets you run a primary key count.

    • Sum lets you add up all purchases made by a customer over one year.

    • Maximum value lets you find the customers having purchased the most “n” products.

    • Minimum value lets you sort through customers and find those having subscribed to an offer most recently.

    • Average. This function lets you calculate the average age of your recipients.

      The Distinct box lets you recover unique and non-zero values of a column. For example, you can recover all of a recipient’s tracking logs and these tracking logs are changed to the value 1 since they all concern the same recipient.

  3. Expression opens the Edit the expression window. This lets you detect telephone numbers with too many figures, likely to be input errors.

    For a list of all available functions, refer to List of functions.

List of functions

If an Expression type formula is chosen, you will be taken to the “edit the expression” window. Various categories of functions can be associated to the available fields: Aggregates, String, Date, Numerical, Currency, Geomarketing, Windowing function and Others.

The expression editor looks like this:

It lets you select fields in the database tables and add advanced functions to them. The following functions are available:

Aggregates

Name
 Description
 Syntax
Avg
 Returns the average of a number type column
 Avg(<value>)
Count
 Counts the non-null values of a column
 Count(<value>)
CountAll
 Counts the values returned (all fields)
 CountAll()
Countdistinct
 Counts the distinct non-null values of a column
 Countdistinct(<value>)
Max
 Returns the maximum value of a number, string, or date type column
 Max(<value>)
Min
 Returns the minimum value of a number, string or date type column
 Min(<value>)
StdDev
 Returns the standard deviation of a number, string or date column
 StdDev(<value>)
Sum
 Returns the sum of the values of a number, string, or date type column
 Sum(<value>)

String

Name
 Description
 Syntax
AllNonNull2
 Indicates if all parameters are non-null and not empty
 AllNonNull2(<string>, <string>)
AllNonNull3
 Indicates if all parameters are non-null and not empty
 AllNonNull3(<string>, <string>, <string>)
Ascii
 Returns the ASCII value of the first character in the string.
 Ascii(<string>)
Char
 Returns the character corresponding to the 'n' ASCII code
 Char(<number>)
Charindex
 Returns the position of string 2 in string 1.
 Charindex(<string>, <string>)
GetLine
 Returns the nth (from 1 to n) line of the string
 GetLine(<string>)
IfEquals
 Returns the third parameter if the first two parameters are equal. If not, returns the last parameter
 IfEquals(<string>, <string>, <string>, <string>)
IsMemoNull
 Indicates if the memo passed as a parameter is null
 IsMemoNull(<memo>)
JuxtWords
 Concatenates the strings passed as parameters. Adds spaces between the strings if necessary.
 JuxtWords(<string>, <string>)
JuxtWords3
 Concatenates the strings passed as parameters. Adds spaces between the strings if necessary
 JuxtWords3(<string>, <string>, <string>)
LPad
 Returns the completed string on the left
 LPad(<string>, <number>, <character>)
Left
 Returns the first n characters of the string
 Left(<string>, <number>)
Length
 Returns the length of the string
 Length(<string>)
Lower
 Returns the string in lowercase
 Lower(<string>)
Ltrim
 Removes spaces to the left of the string
 Ltrim(<string>)
Md5Digest
 Returns an hexadecimal representation of the MD5 key of a string
 Md5Digest(<string>)
MemoContains
 Specifies whether the memo contains the string passed as a parameter
 MemoContains(<memo>, <string>)
RPad
 Returns the completed string on the right
 RPad(<string>, <number>, <character>)
Right
 Returns the last n characters of the string
 Right(<string>)
Rtrim
 Removes spaces to the right of the string
 Rtrim(<string>)
Smart
 Returns the string with the first letter of each word in capitals
 Smart(<string>)
Substring
 Extracts the substring starting at character n1 of the string and of length n2
 Substring(<string>, <offset>, <length>)
ToString
 Converts the number to a string
 ToString(<number>, <number>)
Upper
 Returns the string in capitals
 Upper(<string>)
VirtualLink
 Returns the foreign key of a link passed as a parameter if the other two parameters are equal
 VirtualLink(<number>, <number>, <number>)
VirtualLinkStr
 Returns the foreign (text) key of a link passed as a parameter if the other two parameters are equal
 VirtualLinkStr(<string>, <number>, <number>)
dataLength
 Returns the string size
 dataLength(<string>)

Date

Name
 Description
 Syntax
AddDays
 Adds a number of days to a date
 AddDays(<date>, <number>)
AddHours
 Adds a number of hours to a date
 AddHours(<date>, <number>)
AddMinutes
 Adds a number of minutes to a date
 AddMinutes(<date>, <number>)
AddMonths
 Adds a number of months to a date
 AddMonths(<date>, <number>)
AddSeconds
 Adds a number of seconds to a date
 AddSeconds(<date>, <number>)
AddYears
 Adds a number of years to a date
 AddYears(<date>, <number>)
DateOnly
 Returns the date only (with time at 00:00)*
 DateOnly(<date>)
Day
 Returns the number representing the day of the date
 Day(<date>)
DayOfYear
 Returns the number of the day in the year of the date
 DayOfYear(<date>)
DaysAgo
 Returns the date corresponding to the current date minus n days
 DaysAgo(<number>)
DaysAgoInt
 Returns the date (integer yyyymmdd) corresponding to the current date minus n days
 DaysAgoInt(<number>)
DaysDiff
 Number of days between two dates
 DaysDiff(<end date>, <start date>)
DaysOld
 Returns the age in days of a date
 DaysOld(<date>)
GetDate
 Returns the current system date of the server
 GetDate()
Hour
 Returns the hour of the date
 Hour(<date>)
HoursDiff
 Returns the number of hours between two dates
 HoursDiff(<end date>, <start date>)
Minute
 Returns the minutes of the date
 Minute(<date>)
MinutesDiff
 Returns the number of minutes between two dates
 MinutesDiff(<end date>, <start date>)
Month
 Returns the number representing the month of the date
 Month(<date>)
MonthsAgo
 Returns the date corresponding to the current date minus n months
 MonthsAgo(<number>)
MonthsDiff
 Returns the number of months between two dates
 MonthsDiff(<end date>, <start date>)
MonthsOld
 Returns the age in months of a date
 MonthsOld(<date>)
Second
 Returns the seconds of the date
 Second(<date>)
SecondsDiff
 Returns the number of seconds between two dates
 SecondsDiff(<end date>, <start date>)
SubDays
 Subtracts a number of days from a date
 SubDays(<date>, <number>)
SubHours
 Subtracts a number of hours from a date
 SubHours(<date>, <number>)
SubMinutes
 Subtracts a number of minutes from a date
 SubMinutes(<date>, <number>)
SubMonths
 Subtracts a number of months from a date
 SubMonths(<date>, <number>)
SubSeconds
 Subtracts a number of seconds from a date
 SubSeconds(<date>, <number>)
SubYears
 Subtracts a number of years from a date
 SubYears(<date>, <number>)
ToDate
 Converts a date + time as a date
 ToDate(<date + time>)
ToDateTime
 Converts a string to a date + time
 ToDateTime(<string>)
TruncDate
 Rounds a date+time to the nearest second
 TruncDate(@lastModified, <number of seconds>)
TruncDateTZ
 Rounds a date + time to a given precision expressed in seconds
 TruncDateTZ(<date>, <number of seconds>, <time zone>)
TruncQuarter
 Rounds a date off to the quarter
 TruncQuarter(<date>)
TruncTime
 Rounds the time part up to the nearest second
 TruncTim(e<date>, <number of seconds>)
TruncWeek
 Rounds a date off to the week
 TruncWeek(<date>)
TruncYear
 Rounds a date + time to January 1st of the year
 TruncYear(<date>)
TruncWeek
 Returns the number representing the day in the week of the date
 WeekDay(<date>)
Year
 Returns the number representing the year of the date
 Year(<date>)
YearAnd Month
 Returns the number representing the year and month of the date
 YearAndMonth(<date>)
YearsDiff
 Returns the number of years between the two dates
 YearsDiff(<end date>, <start date>)
YearsOld
 Returns the age in years of a date
 YearsOld(<date>)
NOTE

Note that the Dateonly function takes into account the server’s timezone, not the operator’s.

Numerical

Name
 Description
 Syntax
Abs
 Returns the absolute value of a number
 Abs(<number>)
Ceil
 Returns the lowest integer greater than or equal to a number
 Ceil(<number>)
Floor
 Returns the greatest integer greater than or equal to a number
 Floor(<number>)
Greatest
 Returns the greater of two numbers
 Greatest(<number 1>, <number 2>)
Least
 Returns the smaller of two numbers
 Least(<number 1>, <number 2>)
Mod
 Returns the remainder of the integer division of n1 by n2
 Mod(<number 1>, <number 2>)
Percent
 Returns the ratio of two numbers expressed as a percentage
 Percent(<number 1>, <number 2>)
Random
 Returns the random value
 Random()
Round
 Rounds off a number to n decimals
 Round(<number>, <number of decimals>)
Sign
 Returns the sign of the number
 Sign(<number>)
ToDouble
 Converts an integer to a float
 ToDouble(<number>)
ToInt64
 Converts a float to a 64 bit integer
 ToInt64(<number>)
ToInteger
 Converts a float to an integer
 ToInteger(<number>)
Trunc
 Truncates n1 to n2 decimals
 Trunc(<n1>, <n2>)
  1. Currency
Name
 Description
 Syntax
ConvertCurrency
 Converts an amount in a source currency to an amount in a target currency
 ConvertCurrency(<amount>, <source currency>, <target currency>, <conversion date>)
FormatCurrency
 Formats the amount displayed based on the selected currency settings
 FormatCurrency(<amount>, <currency>)

Geomarketing

Name
 Description
 Syntax
Distance
 Returns the distance between two points defined by their longitude and latitude, expressed in degrees.
 Distance(<Longitude A>, <Latitude A>, <Longitude B>, <Latitude B>)

Others

Name
 Description
 Syntax
Case
 Returns value 1 if the condition is true. If not, it returns value 2.
 Case(When(<condition>, <value 1>), Else(<value 2>))
ClearBit
 Deletes the Flag in the value
 ClearBit(<identifier>, <flag>)
Coalesce
 Returns value 2 if value 1 is zero or null, otherwise returns value 1
 Coalesce(<value 1>, <value 2>)
Decode
 Returns value 3 if value 1 = value 2. If not returns value 4.
 Decode(<value 1>, <value 2>, <value 3>, <value 4>)
Else
 Returns value 1 (may only be used as a parameter of the case function)
 Else(<value 1>, <value 2>)
GetEmailDomain
 Extracts the domain from an email address
 GetEmailDomain(<value>)
GetMirrorURL
 Retrieves the URL of the mirror page server
 GetMirrorURL(<value>)
Iif
 Returns value 1 if the expression is true. If not, returns value 2
 Iif(<condition>, <value 1>, <value 2>)
IsBitSet
 Indicates whether the Flag is in the value
 IsBitSet(<identifier>, <flag>)
IsEmptyString
 Returns value 2 if string 1 is empty, otherwise returns value 3
 IsEmptyString(<value 1>, <value 2>, <value 3>)
NoNull
 Returns the empty string if the argument is NULL
 NoNull(<value>)
RowId
 Returns the line number
 RowId
SetBit
 Forces the Flag in the value
 SetBit(<identifier>, <flag>)
ToBoolean
 Converts a number into a Boolean
 ToBoolean(<number>)
When
 Returns value 1 if the expression is true. If not, it returns value 2 (may only be used as a parameter of the case function)
 When(<condition>, <value 1>)

Windowing functions

Name
 Description
 Syntax
Desc
 Applies a descending sort
 Desc(<value 1>)
OrderBy
 Sorts the result within the partition
 OrderBy(<value 1>)
PartitionBy
 Partitions the result of a query on a table
 PartitionBy(<value 1>)
RowNum
 Generates a line number based on the table partition and on a sorting sequence.
 RowNum(PartitionBy(<value 1>), OrderBy(<value 1>))
Previous page
Next page

On this page