Skip to main content
  • 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 TypeDescriptionNotes/Example
Int32 bit integerinteger not larger than 2**31 -1. Example: 10
Long64 bit integerinteger that can be larger than 2**31 -1. Example: 10L or 10l
Float32 bit floating pointExample: 10.5F or 10.5f or 10.5e2f or 10.5E2F
Double64 bit floating pointExample: 10.5 or 10.5e2 or 10.5E2
Chararray (String)Character arrayAny character string. Example: ‘hello world’
BinaryByte array
Booleantrue or falseExample: true/false (case insensitive)
DatetimeDate-time valueISO 8601 format YYYY-MM-DDThh:mm:ss[.mmm]. Example: 1970-01-01T00:00:00.000+00:00
TupleAn ordered set of fields(field1, field2). Example: (19,2)
BagA collection of tuples{(field1, field2), (field1, field2)} . Example: {(19,2), (18,1)}
MapA set of key value pairs[key#value]. Example: [integrate.io ETL#rocks]
If you want to convert one data type to another, you can cast it, as long as the 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 this syntax: (float)myint. See the Cast Operators section below for more information.

​
Nulls

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:
OperatorImpact
Comparison or arithmetic operatorIf either subexpression is null, the result is null.
is null operatorIf the tested value matches the condition, returns true; otherwise, returns false.
BincondIf 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.

​
Functions

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.

​
Operators

Arithmetic operators are applied to any numeric fields and constants to derive a numeric expression. Available operators are:
OperatorSymbolNotes/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:
OperatorFunction NameNotes/Example
ConcatenationCONCATCONCAT(fieldl1, field2)
SubstringSUBSTRINGSUBSTRING('California', 4, 7)
Comparison operators are used in conditions:
OperatorSymbolNotes/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 matchingmatchesExpression matches a regular expression Example: field1 matches '.*data.*'
Null operators are used in conditions:
OperatorSymbolNotes/Example
expression is a null valueis nullfield1 is null
expression is not a null valueis not nullfield1 is not null
Boolean operators are applied to a condition to derive a different result. Available operators are:
OperatorSymbolNotes/Example
ANDandfield1==5 and field2>20
ORorfield1==5 or field2>20
NOTnotfield1==5 and not field2>20
Dereference operators are applied to complex data types to extract values from them:
OperatorSymbolNotes/Example
Tuple derefencetuple.$0Returns fields by ordinal number (starts at 0). For example: STRSPLIT('Mary had a little lamb',' ').$1 returns had
Map dereferencemap#‘key’Returns the value for the key. If the key does not exist, returns empty string. For example: QueryStringToMap('https://someurl/?origin=135&id=321')#'id'

​
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 TypeTo Data Type
intNumeric, Chararray
longNumeric, Chararray
floatNumeric, Chararray
doubleNumeric, Chararray
chararrayNumeric, Boolean
binaryAll
booleanChararray
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

Conditions can be specified in expressions using either:

​
Case

Specifies outcomes based on either the value of an expression or based on conditions.
The syntaxes are: 
CASE expression  
  WHEN value then return\_value  
  WHEN ....  
  ELSE return\_value  
ENDCASE   
  WHEN condition THEN return\_value  
  WHEN ....  
  ELSE return\_value  
END

​
Examples:

First syntax example: 
CASE City  
  WHEN 'Paris' THEN 'Baguette'  
  WHEN 'London' THEN 'Fish and chips'  
  ELSE 'Coffee'  
END
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: 
CASE  
  WHEN A>B THEN 'A is greater than B'  
  WHEN A<B THEN 'B is greater than A'  
  ELSE 'A and B are equal'  
END
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.

​
Bincond

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  )

​
Example:

( 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.
Last modified on April 20, 2026