Formula operators

This section describes the use of field references, strings, arrays and operators in formulas. Formulas are constructed in the Mapping dashboard which is part of the Pipeline dashboard.

Formulas consists of field references and constants separated by operators. The value of a formula is calculated from left to right, that is, they are left associative. Operations are grouped from the left.

The following illustrates an object called total_price. This is resolved from left to right where addition is performed first, then the multiplication.

Value of object determined from left to right

Any field expression can be used as an operand for an operator, as shown in the example above.

Data types

The following table describes the data types used in Xapix.

Type

Description

Null

Same as in Excel (NULL).

Boolean

Same as in Excel (TRUE/FALSE). Additionally, Axel-f understands True/False/true/false

Boolean numbers

Integers, Floats, Exponential form.

Strings

Double or single quotes. ('Some String', "Some String").

Arrays

Any data in curly brackets ({1, 2, TRUE})

Strings

Strings are wrapped in either single quotes or double quotes. A leading dot is recommended.

# Wrap strings in single or double quotes.
.'string'
."string"
# Wrap strings with spaces in single or double quotes.
.'stringwith spaces' // single quotes
."string with spaces" // double quotes
# Alternately, you can use escape characters
.string\ with\ spaces

Arrays

Arrays are indicated by the use of bracket notation. This notation can also be used for nested arrays as well.

The following shows several examples using bracket notation to parse all elements in an array.

# Expressions can have arrays at any child element.
# Selects all elements of the list array.
.api-org-data-2-5.body.list.[]
# Selects all elements from a nested arrays.
.unit[][]
# Selects path elements from the unit array.
.unit[].path
# Selects the attribute element from nested arrays.
.unit[].path[].attribute
# Selects the attribute only from the first element from an array.
.unit.path[1].attribute
# Calculate then select the element of the unit array.
.unit[SUM(x,10].attribute

Field references

In Axel-f, an object is a collection of field/value pairs. For example:

# In the following object, field is "dt", value is 1582038000.
"dt": 1582038000,
# The following object, field is "sys", value is the structure
# containing the field "pod" and value "d"
"sys": {
"pod": "d"
},

A field reference can be any character except the following:

  • space

  • single/double quote

  • dot

  • comma

  • opening/closing square or round brackets

  • operators

Fields that contain characters that cannot be used as field references must be wrapped into string literals.

# The field bar > baz contains the > character. It must be wrapped into a string.
.'bar > baz'[0].foo
# The field foo -> bar contains ->. It must be wrapped into a string.
# for an object {"foo -> bar" {"baz" 1}}
# we need to get number 1
.'foo -> bar'.baz

Some functions such as FILTER or SORT can return nested data structures. This data can be used as a root reference object:

.FILTER(FN(item, item.x), _)[0].x
# The following array
[{:x 2} {:x 1} {:x 3}]
# returns the following value
1

Operators

. (dot) operator

We recommend the use of use of leading dots in all formulas as they increase the speed of Xapix processing. Leading dots are required when an expression begins with a string or an array.

# The following is acceptable use without leading dot.
foo.bar.baz
# However, the use of a leading dot is recommended.
.foo.bar.baz
# Expressions beginning with arrays require a leading dot.
.[0].foo
# Expression beginning with a string require a leading dot.
."key with spaces".bar
# Child elements are denoted by the dot operator.
.getdriversbyid.body.data.id

Array operators

: operator

  • Purpose: Represents a range of values in an array.

  • Syntax: operand1:operand2

  • Example: 1:5 evaluates to [1,2,3,4]

, operator

  • Purpose: Divides elements in an array. Single spaces are ignored.

  • Syntax: [element1, element2, element3]

  • Example: [1,2,3]

  • Example with spaces: [1, 2, 3] same as [1,2,3]

Arithmetic operators

* operator

  • Purpose: multiplies two operands.

  • Syntax: operand1 * operand2

/ operator

  • Purpose: divides two operands.

  • Syntax: operand 1 / operand2

+ operator

  • Purpose: sums two operands.

  • Syntax: operand1 + operand2

- operator

  • Purpose: subtracts right operand from left operand.

  • Syntax: operant1 - operand2

^ operator

  • Purpose: Raises the number to the power of an exponent.

  • Syntax: operand^exponent

Concatenation operators

& operator

  • Purpose: Appends (concatenates) two operands or strings together

  • Syntax: operand1 & operand2

  • Related function: CONCATENATE

Not to be confused with CONCAT, which appends two arrays.

Comparison operators

> operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is more than right operand.

  • Syntax: operand1 > operand2

< operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is less than right operand.

  • Syntax: operand1 < operand2

>= operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is less than or equal to the right operand.

  • Syntax: operand1 > operand2

<= operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is less than or equal to the right operand.

  • Syntax: operand1 <= operand2

= operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is equal to the right operand.

  • Syntax: operand1 = operand2

<> operator

  • Purpose: Compares two operands and evaluates to true or false. Left operand is not equal to the right operand.

  • Syntax: operand1 <> operand2

% operator

  • Purpose: Remainder operator that evaluates the remainder after dividing left operand by right operand.

  • Syntax: operand1 % operand2

  • Example: (2.4% => 0.024)

Logical operators

! operator

  • Purpose: Computes a logical negation on an operand or expression

  • Syntax: !operand1

  • Example: !24

Operator precedence

Axel-f functions have the same operator precedence as in Excel. When more than one operator occurs in an expression, the expression is evaluated based on order precedence. In general, arithmetic operators are evaluated first followed by comparison operators.

Changing operator precedence

To change a precedence of operators, enclose in parenthesis the part of the formula to be calculated first.

((2+2)*2 => 8)