# Using Expressions in Xplenty

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:

- Open the required component (from one of the components listed above).
- In the drop-down list for the field you wish to edit, select edit.
- Type in an expression.
- You may press CTRL+SPACEBAR to use auto-complete: A list of field names (available in this component), functions and data types appears.
- 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] |

## 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:

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.

## Functions

To view full list of functions click ** here**.

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:

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:`QueryStringToMap('http://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 Type | To Data Type |
---|---|

int | Numeric, Chararray |

long | Numeric, Chararray |

float | Numeric, Chararray |

double | Numeric, Chararray |

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 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 valuethenreturn_valueWHEN.... ELSEreturn_valueEND

CASE WHEN conditionTHENreturn_valueWHEN.... ELSEreturn_valueEND

*Examples:*

*First syntax example:*

CASE City WHEN 'Paris' THEN 'Baguette' WHEN 'London' THEN 'Fish and chips' ELSE 'Coffee' ENDIf 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.