Using Expressions in Xplenty

You can write expressions to transform fields or create new fields using the available functions, operators and conditions. 

Expressions can be used in the following components where user entry is allowed:

  • Select fields
  • Filter conditions
  • Package variables

You can use the expression editor to write expressions. You can also directly enter the expression in the field, without using the editor.

To use the expression editor:

  1. Open the required component (from one of the components listed above).
  2. In the field you wish to edit, click on the edit icon.
  3. Type in an expression.
  4. A list of field names (available in this component), functions and data types appears.
  5. Scroll to the required item and click to add it to the expression.

Read more:

Data Types

You can use the following data types in expressions.

Data Type Description Notes/Example
Int 32 bit integer integer not larger than 2**31 -1. Example: 10
Long 64 bit integer integer that can be larger than 2**31 -1. Example: 10L or 10l
Float 32 bit floating point Example: 10.5F or 10.5f or 10.5e2f or 10.5E2F
Double 64 bit floating point Example: 10.5 or 10.5e2 or 10.5E2
Chararray (String) Character array Any character string. Example: 'hello world'
Binary Byte array
Boolean true or false Example: true/false (case insensitive)
Datetime Date-time value ISO 8601 format YYYY-MM-DDThh:mm:ss[.mmm]. Example: 1970-01-01T00:00:00.000+00:00
Tuple An ordered set of fields (field1, field2). Example: (19,2)
Bag A collection of tuples {(field1, field2), (field1, field2)} . Example: {(19,2), (18,1)}
Map A set of key value pairs [key#value]. Example: [xplenty#rocks]


Null values represent a value that is unknown or non-existent. Note that is different than an empty string and a null string is not equal to an empty string.

Nulls can occur naturally in the data source or can be the result of an operation, such as division by zero or outer join.

A null value in expression data has these impacts:

Operator Impact
Comparison or arithmetic operator If either subexpression is null, the result is null.
is null operator  If the tested value matches the condition, returns true; otherwise, returns false.
Bincond If the expression results to a null value, bincond returns null,

Null values can be converted to a data type, just like any other expression. For example, int(null) casts to integer.


To view full list of functions click here.

You can use functions in components to further manipulate the results output by a component. Functions can be added to any component field that can be edited directly (not just selected from a drop-down list).

The best method to add functions is by using the expression editor, which provides onscreen information on functions and performs validation on the completed expression.

These functions are available to transform the field. Use a function that matches the data type of the field. Note the following when using the function.

  • Function names are case sensitive and therefore have to be entered exactly as shown in the table.
  • Place the cursor at the start of the field you want to apply the function and then select the function from the list or enter the function name directly. Then add parentheses and additional parameters as shown in the syntax.


Arithmetic operators are applied to any numeric fields and constants to derive a numeric expression. Available operators are:

Operator Symbol Notes/Example
Addition + Field1+Field2+10
Subtraction - field1-field2
Multiplication * field1*5
Division / field1/field2
Modulo % Returns the remainder of a number divided by another number. Example: field1%5

There are special String functions to derive a string from a string expression. Examples of operators are:

Operator Function Name Notes/Example
Concatenation CONCAT CONCAT(fieldl1,field2)
Substring SUBSTRING SUBSTRING('California',4,7)

Comparison operators are used in conditions:

Operator Symbol Notes/Example
Equal == Field1==Field2
Not equal != field1!=field2
Greater than > field1>5
Less than < field1<6
Greater than or equal to >= field1>=5
Less than or equal to <= field1<=5
pattern matching matches Expression matches a regular expression         Example: field1 matches '.*data.*'

Null operators are used in conditions:

Operator Symbol Notes/Example
expression is a null value is null field1 is null
expression is not a null value is not null field1 is not null

Boolean operators are applied to a condition to derive a different result. Available operators are:

Operator Symbol Notes/Example
AND and field1==5 and field2>20
OR or field1==5 or field2>20
NOT not field1==5 and not field2>20

Dereference operators are applied to complex data types to extract values from them:

Operator Symbol Notes/Example
Tuple derefence tuple.$0 Returns fields by ordinal number (starts at 0). For example:
STRSPLIT('Mary had a little lamb',' ').$1 returns had
Map dereference map#'key' Returns the value for the key. If the key does not exist, returns empty string. For example:

Cast Operators

Cast operators are used to cast or convert data from one data type to another, as long as conversion is supported. For example, suppose you have an integer field, myint, which you want to convert to a float. You can cast this field from int to float using (float)myint.

These are the cast conversions that are supported. Numeric includes the numeric data types: integer, long, float and double.

Data Type To Data Type
int Numeric, Chararray
long Numeric, Chararray
float Numeric, Chararray
double Numeric, Chararray
Numeric, Boolean
binary All
boolean Chararray

Implicit casts to convert data to the target data type are performed where the cast conversion is supported. In the statement field1+1, field1 is converted to integer, if that conversion is supported .

In some cases there are functions to cast that data type. These functions are used to cast a datetime field to Chararray (string) or a chararray to datetime:

In addition to the string datetime casts, these functions are used to cast a string to one of the numeric formats:


Conditions can be specified in expressions using either:


Specifies outcomes based on either the value of an expression or based on conditions. 
The syntaxes are:

CASE expression
  WHEN  value then return_value
  ELSE return_value

CASE WHEN condition THEN return_value WHEN.... ELSE return_value END


First syntax example:
WHEN 'Paris' THEN 'Baguette'
WHEN 'London' THEN 'Fish and chips'
ELSE 'Coffee'
If the value of the field City is "Paris" the above example returns the string "Baguette". 
If the value of the field City is "London" the above example returns the string "Fish and chips".
Any other case, it returns "Coffee".

Second syntax example:
WHEN A>B THEN 'A is greater than B'
WHEN A<B THEN 'B is greater than A' 
ELSE 'A and B are equal'

If the variable A is larger then B, the string "A is greater than B" is returned.
If the variable B is larger then A, the string "B is greater than A" is returned.
Otherwise, the string "A and B are equal" is returned.

Note: If a WHEN condition compares a value to NULL, NULL is returned and the following conditions are ignored. Consider using COALESCE to eliminate NULL in a field used in a WHEN condition.


Specifies binary outcomes based on the value of an expression, but is more limited and not as readable as the Case statement. The syntax is:

 (expression?true result:false result)


(10<100? 'funny but true':'sad and wrong')
Returns the string: "funny but true".

Note: If an expression compares a value to NULL, NULL is returned.

Creating packages

  1. Creating a new package in New Xplenty
  2. Creating a workflow
  3. Working in the new package designer
  4. Validating a package
  5. Using components: Amazon Redshift Source
  6. Using components: Bing Ads Source
  7. Using components: Database Source
  8. Using components: Facebook Ads Insights Source
  9. Using components: File Storage Source
  10. Using components: Google Adwords source
  11. Using components: Google Analytics Source
  12. Using components: Google BigQuery Source
  13. Using components: Google Cloud Spanner Source
  14. Using components: MongoDB Source
  15. Using components: NetSuite Source
  16. Using components: Salesforce source
  17. Using components: Rest API Source
  18. Using components: Aggregate Transformation
  19. Using components: Assert Transformation
  20. Using components: Clone transformation
  21. Using components: Cross Join Transformation
  22. Using components: Distinct Transformation
  23. Using components: Filter Transformation
  24. Using components: Join Transformation
  25. Using components: Limit Transformation
  26. Using components: Rank Transformation
  27. Using components: Sort Transformation
  28. Using components: Union Transformation
  29. Using components: Window Transformation
  30. Using components: Sample Transformation
  31. Using components: Cube transformation
  32. Using components: Amazon Redshift Destination
  33. Using components: Database Destination
  34. Using components: File Storage Destination
  35. Using components: Google BigQuery Destination
  36. Using components: Google Spanner Destination
  37. Using components: MongoDB Destination
  38. Using components: Salesforce Destination
  39. Using components: Snowflake Destination
  40. Using Components: Rest API Destination
  41. Using and setting variables in your packages
  42. System and pre-defined variables
  43. Using pattern-matching in source component paths
  44. Using ISO 8601 string functions
  45. Using Expressions in Xplenty
  46. Xplenty Functions

Feedback and Knowledge Base