Qualtrics Platform
Customer Journey Optimizer
XM Discover
Qualtrics Social Connect

Building Expressions

About Building Expressions

Expressions are used in XM Discover to filter your data and to transform data fields. For example, you can use expressions to change date field formatting, evaluate data for empty records, and perform mathematical calculations.

Expressions can contain 4 components:

Constants: You can use constant numbers, text strings, and dates to perform math and comparison operations.
Fields: You can use numeric, text, and date fields available via a specific data connector to perform math and comparison operations on those fields.
Functions: You can use functions to perform transformations on your fields and constants.
Operators: You can use operators to perform math operations and compare your fields and constants.

Constants

Constants are numbers, text strings, and dates that you can use to perform math operations and comparisons. For example, you can use a constant date, such as the date you launched your annual CX survey, to calculate how old a survey taker’s response is.

Notes on using constants:

Text strings are treated as case-sensitive. Use UPPER or LOWER functions as a workaround for case-sensitivity.
Text strings and dates must be put inside quotation marks.
Numbers don’t need quotation marks (a number inside quotation marks is treated as text).

Fields

Fields are the data fields available from your inbound data connector. This includes numeric, text string, and date fields, which you can use to perform math operations and comparisons. For example, if your dataset contains a field for a customer’s birthday, you can calculate how old the customer is.

To add a field, drag it from the Fields section onto the Expression box.

Field types are labeled with the following icons:

for date fields
for numeric fields
for text and verbatim fields

Functions

You can use numeric, text, and date functions listed in this section.

To add a function, drag it from the Functions section onto the Expression box.

Function types are labeled with the following icons:

for date functions
for numeric functions
for text functions

ABS

Returns the absolute value of a number.

Syntax: ABS(number)

Example: ABS(-22) resolves to 22.

COALESCE

Returns the first non-null value in a list.

Qtip: An empty string is considered to be null. If all values are null, the function also returns null.

Syntax: COALESCE(value1, value2, value3)

Example: COALESCE(“”, “”, “Jane”, “”, “Paul”) resolves to Jane.

CEILING

Returns the rounded up value of a number.

Syntax: CEILING(number)

Example: CEILING(21.2) resolves to 22.

CONCAT

Joins several text strings into one.

Syntax: CONCAT(“string 1”, “string 2”, “string 3”)

Example: CONCAT(“Hello”, “, “, “John”) resolves to Hello, John.

CONTAINS

Returns true if a text string (“within text”) contains another text string (“find text”). Otherwise returns false.

Syntax: CONTAINS(“within text”, “find text”)

Example: CONTAINS(“one, two, three”, “two”) resolves to true.

CONVERT_TO_TIMEZONE

Converts date and time from one time zone to another.

Syntax: CONVERT_TO_TIMEZONE(“date and time”, “time zone to convert from”, “time zone to convert to”)

Notes on date formatting:

Provide the time zones using their tz database names.
Provide the date and time in ISO 8601 or as yyyy-mm-dd hh:mm:ss.
If date and time are provided with time zone information, it is ignored in favor of the “time zone to convert from” parameter.
The converted date uses the ISO 8601 format. Use the TODATE function if you need to change that.

Example: CONVERT_TO_TIMEZONE(2020-03-19 15:15:00, “Europe/London”, “Europe/Berlin”) resolves to 2020-03-19T16:15:00Z.

DATETONUMBER

Converts a date field to a Unix timestamp.

Qtip: Unix timestamp is the number of milliseconds that have elapsed since January 1, 1970.

Syntax: DATETONUMBER(“date”)

Qtip: A reverse function called NUMBERTODATE is available.

Example: DATETONUMBER(“2020-10-20T09:12:00.670Z”) resolves to 1603185120670.

DATETOTEXT

Converts a date field to a date string in the requested format.

Syntax: DATETOTEXT(“date”, “date format”)

Example: DATETOTEXT(NOW(), “MMMM DD YYYY, h:mm:ss”) resolves to April 29 2020, 3:21:05.

DAYOFWEEK

Returns the day of the week corresponding to a date.

Syntax: DAYOFWEEK(“date”)

Example: DAYOFWEEK(“2019-12-19T20:16:05.602Z”) resolves to Thursday.

DYNAMICLOOKUP

Replaces field’s values using a dynamically loaded lookup table. Lookup values are taken from the field specified in the first argument (“field name”).

Syntax: DYNAMICLOOKUP(“field name”, “value”)

Qtip: Transformations within the DYNAMICLOOKUP function are not allowed.

Example: DYNAMICLOOKUP(“QID15”, “10”) resolves to Excellent.

ELEMENTOFARRAY

Extract the nth value from an array, then map array values to attributes in XM Discover.

Syntax: ELEMENTOFARRAY([“array element 1”, “array element 2”, “array element 3”], number)

Notes:

If the input is not an array, the function throws an error.
If the number is greater than the size of an array, the function returns null. Numbers in increasing order start from 0.

Qtip: This function is particularly useful for XM Discover Link Connector jobs.

Example: ELEMENTOFARRAY([“a”, “b”, “c”], 1) resolves to b.

FIELD

Wraps field names that contain spaces or dots.

Syntax: FIELD(“field name”)

Example: FIELD(“Text Processed”) returns the value of the field named “Text Processed.”

FIND

Locates one text string (“find text”) within another text string (“within text”) and returns the number of the starting position of “find text” from the first character of “within text” (starting from 1).

Allows specifying the order of the character in “within text” at which to start the search (starting from 1). If you omit order_number, it is assumed to be 1.

Returns -1 if “find text” is not found in “within text.”

Syntax: FIND(“find text”, “within text”, order_number)

Qtip: The function is case-sensitive. If you want to run a case-insensitive search, use the SEARCH function.

Example: FIND(“easy”, “easy come, easy go”, 3) resolves to 12.

FINDFIRST

Locates the first occurrence of a text string (“find text”) within another text string (“within text”) and returns the number of the starting position of “find text” from the first character of “within text” (starting from 1).

Qtip: The function is case-sensitive; returns -1 if “find text” is not found in “within text.”

Syntax: FINDFIRST(“within text”, “find text”)

Example: FINDFIRST(“easy come, easy go”, “easy”) resolves to 1.

FINDLAST

Locates the last occurrence of a text string (“find text”) within another text string (“within text”) and returns the number of the starting position of “find text” from the first character of “within text” (starting from 1).

Qtip: The function is case-sensitive; returns -1 if “find text” is not found in “within text.”

Syntax: FINDLAST(“within text”, “find text”)

Example: FINDLAST(“easy come, easy go”, “easy”) resolves to 12.

FLOOR

Returns the rounded down value of a number.

Syntax: FLOOR(number)

Example: FLOOR(21.9) resolves to 21.

GENERATE_ID

Generates a unique ID. This function does not require additional parameters.

Syntax: GENERATE_ID()

Example: GENERATE_ID() resolves to 5ecfdd3fdd4ca4f23c5f2602.

GETDAYSBETWEEN

Returns the number of days between two dates.

Syntax: GETDAYSBETWEEN(“start date”, “end date”)

Example: GETDAYSBETWEEN(“2019-12-19T20:16:05.602Z”, “2019-12-24T20:16:05.602Z”) resolves to 5.

GETHOURSBETWEEN

Returns the number of hours between two dates.

Syntax: GETHOURSBETWEEN(“start date”, “end date”)

Example: GETHOURSBETWEEN(“2019-12-19T20:16:05.602Z”, “2019-12-24T20:16:05.602Z”) resolves to 120.

GETMINUTESBETWEEN

Returns the number of minutes between two dates.

Syntax: GETMINUTESBETWEEN(“start date”, “end date”)

Example: GETMINUTESBETWEEN(“2019-12-19T20:16:05.602Z”, “2019-12-24T20:16:05.602Z”) resolves to 7200.

IF

Returns one value if the statement is true, and another value if the statement is false.

Syntax: IF(statement, “value if true”, “value if false”)

Example: IF(3 > 2, “greater”, “not greater”) resolves to greater.

ISBLANK

Returns TRUE when a field is empty, and FALSE when a field is not empty.

Syntax: ISBLANK(“field name”)

Qtip: Since ISBLANK is a Boolean function, you cannot apply it to an attribute directly. Use it as part of an expression that returns the result in proper format (date, number, or text).

Example: Detect an empty field and return the result as a text string. IF(ISBLANK(“”), “empty”, “filled”) resolves to empty.

Example: Detect a non-empty field and return the result as a number. IF(ISBLANK(“John”), 0, 1) resolves to 1.

LEN

Returns the number of letters in a text string.

Syntax: LEN(“text”)

Example: LEN(“Count me if you can”) resolves to 19.

LOWER

Converts text to lowercase.

Syntax: LOWER(“text”)

Example: LOWER(“HELLO”) resolves to hello.

MD5HASH

Applies MD5 hash to the input data to generate unique IDs based on that data.

Syntax: MD5HASH(“text”)

Example: MD5HASH(“Hello”) resolves to 543d4abcdc64a9a377c959e4b6e35574.

MID

Returns a specific number of characters from a text string, starting at the position you specify.

The position (order_number) starts from 1.

Syntax: MID(“text”, order_number, characters_number)

Example: Return 8 characters starting from 1st character. MID(“practice makes perfect”, 1, 8) resolves to practice.

Example: Return 8 characters starting from 16th character. MID(“practice makes perfect”, 16, 8) resolves to perfect.

MOD

Returns the remainder when a number is divided by a divisor.

Qtip: The result has the same sign as a number.

Syntax: MOD(number, divisor)

Example: MOD(7, 4) resolves to 3.

NOW

Returns the current date and time. This function does not require additional parameters.

Syntax: NOW()

Example: NOW() resolves to 2020-01-29T13:35:09.956Z.

NUMBERTODATE

Converts a Unix timestamp to a date field.Unix timestamp is the number of milliseconds that have elapsed since January 1, 1970.

Syntax: NUMBERTODATE(number)

Qtip: A reverse function called DATETONUMBER is available.

Example: NUMBERTODATE(1603185120670) resolves to 2020-10-20T09:12:00.670Z.

NUMBERTOTEXT

Converts a number to text.

Syntax: NUMBERTOTEXT(number)

Example: NUMBERTOTEXT(21) resolves to 21.

POW

Returns the result of x to the power of y.

Syntax: POW(x, y)

Example: POW(2, 4) resolves to 16.

PROPERCASE

Capitalizes the first letter of a text string.

Syntax: PROPERCASE(“text”)

Example: PROPERCASE(“practice makes perfect”) resolves to Practice makes perfect.

RANDOM

Returns a random real number between 0 and 1. This function does not require additional parameters.

Syntax: RANDOM()

Example: RANDOM() resolves to 0.7669519868005736.

RANDOMBETWEEN

Returns a random integer number between two numbers.

Qtip: This function can return the minimum and maximum numbers.

Syntax: RANDOMBETWEEN(minimum number, maximum number)

Example: RANDOMBETWEEN(1, 10) resolves to 2.

REMOVEHTMLTAGS

Removes HTML tags from a text string.

Syntax: REMOVEHTMLTAGS(“text”)

Example: REMOVEHTMLTAGS(“<html>hello</html>”) resolves to hello.

REPLACE

Replaces part of a text string (“old text”) with a different text string (“new text”) a set number of times (number_of_replacements) based on the occurrence number of “old text” within “text” (occurrence_number).

Syntax: REPLACE(“text”, “old text”, “new text”, occurrence_number, case_sensitivity, number_of_replacements)

Example: REPLACE(“think inside the box”, “inside”, “outside”, 1, false, 1) resolves to think outside the box.

REPLACEBYINDEX

Replaces part of a text string (“old text”) with a different text string (“new text”), based on the number of characters you specify.

The position (order_number) starts from 1.

Syntax: REPLACE(“old text”, order_number, characters_number, “new text”)

Example: REPLACEBYINDEX(“think inside the box”, 7, 6, “outside”) resolves to think outside the box.

REPLACEBYREGEXP

Replaces text values using regular expressions.

Qtip: A regular expression is a sequence of characters that define a search pattern. For a brief introduction, see Microsoft’s .NET Regular Expressions.

Attention: Custom coding features are provided as-is and require programming knowledge to implement. Qualtrics Support does not offer assistance or consultation on custom coding. You can always try asking our community of dedicated users instead. If you’d like to know more about our custom coding services, please contact your Qualtrics Account Executive.

Syntax: REPLACEBYREGEXP(“text”, “regular expression for pattern matching”, “regular expression for replacement value”)

Qtip: Special characters like backslashes (\), double quotes (“), and single quotes (‘) must be escaped (i.e., have a backslash added before them). For example, if you want to use the pattern that matches any whitespace character (\s), you’ll need to escape it with an additional backslash (\\s).

Example: REPLACEBYREGEXP(“Clarabank UK”, “(.)\\s(.)”, “$1;$2”) resolves to Clarabank;UK.

ROUND

Returns the value of a number rounded to the nearest integer.

Syntax: ROUND(number)

Example: ROUND(22.5) resolves to 23.

SEARCH

Allows specifying the order of the character in “within text” at which to start the search (starting from 1). If you omit order_number, it is assumed to be 1.

Returns -1 if “find text” is not found in “within text.”

Syntax: SEARCH(“find text”, “within text”, order_number)

Qtip: The function is case-insensitive. If you want to run a case-sensitive search, use the FIND function.

Example: SEARCH(“EASY”, “easy come, easy go”, 3) resolves to 12.

SUBSTITUTE

Substitutes “old text” with “new text” in a text string.

Allows specifying which occurrence of “old text” you want to replace. If you specify occurrence_number, only that instance of “old text” is replaced. Otherwise, every occurrence of “old text” in text is changed to “new text.”

Syntax: SUBSTITUTE(“text”, “old text”, “new text”, occurrence_number)

Example: Substitute first occurrence of “1” with “2”. SUBSTITUTE(“Quarter 1, 2019”, “1”, “2”, 1) resolves to Quarter 2, 2019.

Example: Substitute second occurrence of “1” with “2”. SUBSTITUTE(“Quarter 1, 2019”, “1”, “2”, 2) resolves to Quarter 1, 2029.

Example: Substitute every occurrence of “1” with “2”: SUBSTITUTE(“Quarter 1, 2019”, “1”, “2”) resolves to Quarter 2, 2029.

SUBSTR

Returns a portion of a text string starting from the first character of the string and up to the specified number of characters (starting from 1).

Syntax: SUBSTR(“text”, order_number)

Example: SUBSTR(“practice makes perfect”, 9) resolves to practice.

TITLECASE

Capitalizes the first letter of each word in a text string.

Syntax: TITLECASE(“text”)

Example: TITLECASE(“practice makes perfect”) resolves to Practice Makes Perfect.

TEXTTODATE

Converts a date string to a date field in the following format: YYYY-MM-DDThh:mm:ssZ.

Syntax: TEXTTODATE(“date”, “date format”)

Qtip: The date format must reflect the format used in the string. Both parameters are required.

Example: TEXTTODATE(“2019-12-19”, “YYYY-MM-DD”) resolves to 2019-12-19T00:00:00.000Z.

TEXTTONUMBER

Converts text to number.

Syntax: TEXTTONUMBER(text)

Example: TEXTTONUMBER(21) to 21.

TRANSLATE

Translates inbound data.

Syntax: TRANSLATE (“source language code”, “target language code”, “text”).

Notes:

This function is only available for accounts with Google Translate API credentials
For a full list of supported language codes, please refer to Google Cloud Translation documentation.

Example: TRANSLATE (“en”, “es”, “Hello world”) resolves to Hola mundo.

TRIMLEFT

Removes spaces from the left end of a text string.

Syntax: TRIMLEFT(“text”)

Example: TRIMLEFT(” hello”) resolves to hello.

TRIMRIGHT

Removes spaces from the right end of a text string.

Syntax: TRIMRIGHT(“text”)

Example: TRIMRIGHT(“hello “) resolves to hello.

UPPER

Converts text to uppercase.

Syntax: UPPER(“text”)

Example: UPPER(“hello”) resolves to HELLO.

Operators

You can use arithmetic and comparison operators listed in the following tables.

Arithmetic Operators

Operator	Description
+	Add
–	Subtract or negate
*	Multiply
/	Divide

For example, the following expression will return the average of the two numeric values.

(RATING1 + RATING2) / 2

Comparison Operators

Operator	Description
==	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
!=	Not equal to

For example, the following expression will return “bad rating” when RATING is less than or equal to 3, and “good rating” when RATING is greater than 3.

IF(RATING <= 3, “bad rating”, “good rating”)

Logical Operators

Use logical operators to specify multiple conditions when using the IF function or job filters.

Qtip: Make sure to use logical operators in lower case.

Operator	Description
and	Condition is true if all the conditions separated by “and” are true.
in	Condition is true if there is a match with any of the listed values. Syntax: in[“value 1”, “value 2”, “value 3”] Qtip: Use this instead of multiple “or” operators.
or	Condition is true if any of the conditions separated by “or” are true.

For example, the following expression will return “Mediterranean” if the COUNTRY field contains any of the specified values. Otherwise it will return “Non-Metiterranean.”

IF(LOWER(COUNTRY) in [“france”, “portugal”, “italy”, “spain”, “greece”, “malta”, “cyprus”], “Mediterranean”, “Non-Mediterranean”)

Syntax Tips

Follow these tips to avoid invalid expressions:

When combining several functions in one expression, make sure the number of opening and closing parentheses matches.
Example: IF(GETDAYSBETWEEN(feedback_date, response_date) > 3, “delayed”, “not delayed”).
For each function used in the expression, all required arguments need to be present. For example, the TODATE function requires both the date and the date format arguments to work correctly.
Be sure to use the correct data type supported by a function or an arithmetic or comparison operation. For example, the ABS function needs a number to work correctly and will not work on a text or date field.
If there are dots (“.”) in your expression, wrap them with the FIELD function and quotation marks (either ‘single’ or “double”). For example, if you have a field named “agentParticipants.0.agentLoginName,” here’s how you wrap it in a potential transformation:
IF(CONTAINS(LOWER(FIELD(“agentParticipants.0.agentLoginName”)), “bot”),”YES”, “NO”).