Documentation Campaign Campaign Web User Interface

Edit expressions expression

Last update: Mon May 13 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

Editing an expression involves manually entering conditions to form a rule. This mode allows you to use advanced functions, which let you manipulate the values used to carry out specific queries such as manipulating dates, strings, numerical fields, sorting, etc.

IMPORTANT

The section below provides information on how to work with the expression editor to build rules. Keep in mind that the syntax used to build rules differ from the one used to add personalization.

Work with the expression editor edit

The expression editor is available from the query modeler Edit expression button, available for the Attribute and Value fields when configuring a custom condition.

Access from the Attribute field

Access from the Value field

{modal="regular"}

{modal="regular"}

The expression editor provides:

An input field (1) in which the expression is defined.
The list of available fields (2) that can be used in the expression and corresponding to the targeting dimension of the query.
Helper functions (3), sorted by category.

Edit the expression by entering an expression directly in the input field. To add a field or a helper function, place your cursor in the expression where you want to add it and click the + button.

{modal="regular"}

When your expression is ready, click the Confirm button. The expression displays in the selected field. To edit it, open the expression editor and make the desired changes.

The example below shows an expression configured for the Value field. To edit it, you need to open the expression editor using the Edit expression button.

{modal="regular"}

Helper functions

The query editing tool allows you to use advanced functions to carry out complex filtering depending on the desired results and the types of manipulated data. The following functions are available:

Aggregate

The aggregate functions are used to perform calculations on a set of values.

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>)

StringAgg

Returns the concatenation of the values of a string type column, separated by the character in the second argument

StringAgg(<Value>, <String>)

Sum

Returns the sum of the values of a number, string, or date type column

Sum(<value>)

Date

The date functions are used to manipulate date or time values.

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>)

ConvertNTZ

Converts timestamp NTZ (timestamp without timezone) into TZ (timestamp with timezone) applying defined session TZ

ConvertNTZ (<date+time>)

DateCmp

Compare two dates

DateCmp(<date>,<date>)

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>)

Oldest

Returns the oldest date in a range

Oldest (<date, 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>)

ToTimestamp

Converts a string to a timestamp

ToTimestamp(<string>)

ToTimeZone

Convert a date + time to time zone

ToTimeZone(<date>,<time zone>)

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>)

WeekDay

Returns a number representing the day in the week of the date (0=Monday, 6=Sunday)

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>)

YearsAgo

Returns the number of years between a given date and the current date

YearsAgo(<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.

Geomarketing

The geomarketing functions are used to manipulate geographical values.

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>)

Numeric

The numeric functions are used to convert text to numbers.

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>)

Others

This table contains the remaining functions available.

Name

Description

Syntax

AESEncrypt

Encrypt string provided in argument

AESEncrypt(<value>)

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>)

NewUUID

Returns a unique ID

NewUUID()

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>)

String

The string functions are used to manipulate a set of strings.

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>)

dataLength

Returns the size in bytes of the string

dataLength(<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>)

Left

Returns the first n characters of the string

Left(<string>, <number>)

Length

Returns the length of the string

Length(<string>)

Line

Extract line n from string

Line(<string>,<number>)

Lower

Returns the string in lowercase

Lower(<string>)

LPad

Returns the completed string on the left

LPad (<String>, <Number>, <Char>)

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>)

NodeValue

Extracts the value of an XML field from its XPath and the field data

NodeValue (<String>, <String>)

Replace

Replaces all occurrences of a specified string value with another string value.

Replace(<String>,<String>,<String>)

Right

Returns the last n characters of the string

Right(<string>)

RPad

Returns the completed string on the right

RPad(<string>, <number>, <character>)

Rtrim

Removes spaces to the right of the string

Rtrim(<string>)

Sha256Digest

Hexadecimal representation of the SHA256 key of a string.

Sha256Digest (<String>)

Sha512Digest

Hexadecimal representation of the SHA512 key of a string.

Sha512Digest (<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>)

Window

Name

Description

Syntax

_Over__

Execute the SQL function call entered as 1st parameter, over Partition or Order By the fields entered as 2nd parameter

_Over_ (<Value>, <Value>)

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>))

recommendation-more-help

c39c2d00-ba9a-424b-adf9-66af58a0c34b