ÃÛ¶¹ÊÓƵ

DocumentationCampaignCampaign 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