Documentation Experience Platform Federated Audience Composition

Edit expressions

Last update: November 13, 2024

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.

Work with the expression editor

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

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 schema, also known as 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.

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.

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

Previous pageBuild your first query

Next pageAudit Trail

Helper functions

Aggregate

Date

Geomarketing

Numeric

Others

String

Window

Learn

Documentation

Certifications

Events

Community

Support

Resources

Adobe Account

Adobe

Edit expressions

Work with the expression editor

Helper functions

Aggregate

Date

Geomarketing

Numeric

Others

String

Window

Experience Cloud