Logic Functions

And

Description

1. Returns TRUE if all the provided arguments are logically true, and FALSE if any of the provided arguments are logically false. This will apply to all elements of a list in the first position.

Syntax

AND(logical_expression_1, [logical_expression_2, ...])

1. 0 is logically FALSE

2. All other numbers (including negative numbers) are logically TRUE

Examples

1. AND(true,true) returns TRUE

2. AND(true,false) returns FALSE

3. AND(0,1) returns FALSE

Apply

Description

1. Applies the parameters as arguments to the function (lambda) provided.
2. Apply is a Lambda Helper function that can be used in conjunction with a Lambda functions.

Syntax

APPLY(LAMBDA, value, [value, ...]

each

Examples

1. APPLY(each + 1, 44) returns 45

2. The next examples use this lambda: LAMBDA(foo + bar, foo, bar)

   • APPLY(LAMBDA(foo + bar, foo, bar),"Hello, ", "Steve") returns Hello, Steve

   • APPLY(LAMBDA(foo + bar, foo, bar),source.first_name, source.last_name) returns the values of the first_name and last_name fields in source data, with no spaces or any other characters separating the two

   • APPLY(LAMBDA(foo + bar, foo, bar),1,2) returns 3 (i.e. the sum of 1 and 2)

Boolean

Description

1. Casts the supplied argument(s) to TRUE or FALSE.

Syntax

BOOL(expression, [expression, ...])

Examples

1. BOOL(true) returns true

2. BOOL(false) returns false

3. BOOL(false,true) returns [false,true]

4. BOOL(source.id=="abc") returns true if the value of the id field in the source data is abc, otherwise it returns false

Coalesce

Description

1. Returns the value of the first non-null expression. The remaining expressions aren't evaluated. An input expression can be any type. There may be multiple input expression types.

Syntax

COALESCE(value, [value,...])

Examples

1. COALESCE(null,1) returns 1

2. COALESCE(source.id, source.order_number) returns the value of the id attribute if id is not null, or the order_number if id is null. If both attributes are null, null is returned.

Filter

Description

1. Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions. This function is used in conjunction with a Lambda function.
2. Filter is a Lambda Helper function that can be used in conjunction with a Lambda function.

Syntax

FILTER(lambda, list)

each

Examples

1. FILTER(each == 23,[1,2,23]) returns [23]

2. FILTER(each!="foo",["foo","bar","baz"]) returns ['bar', 'baz']

Identity

Description

1. Returns the value passed into it.

Syntax

IDENTITY(value)

Examples

1. IDENTITY(source.id) returns the value of the id field in source data

2. IDENTITY(true) returns TRUE

3. IDENTITY(1) returns 1

If

Description

1. Returns one value if a logical expression is TRUE and another if it is FALSE.

Syntax

IF(test, expression_if_true, expression_if_false)

Examples

1. IF(0<1,"zero is less than one","zero is greater than one") returns zero is less than one

2. IF(source.order_no,source.order_no,null) returns the order_no attribute from the source data if it is not null, otherwise it returns null

If Error

Description

1. Returns the value passed in, unless it is an error, in which case it returns the alternative value.

Syntax

IFERROR(value, value_if_error)

Examples

1. IFERROR(5+"D","'D' is not a number") returns 'D' is not a number

2. IFERROR(5+5,"'D' is not a number") returns tbd

Lambda

Description

1. Creates and returns a custom function with a set of names and a formula_expression that uses them. To calculate the formula_expression, you can call the returned function with as many values as the name declares. Lambda functions must be paired with a Lambda Helper function. Lambda Helper functions include:
   • Apply
   • Filter
   • Flatmap
   • List
   • Map
2. Please note that any function can be used in conjunction with a Lambda function, not just Lambda Helper Functions.

Syntax

There are two options for writing Lambda functions. A standard Lambda function is written as follows:

LAMBDA(variable_name, [variable_name, ...] expression)

Additionally, when a function expects a lambda expression as an argument and that lambda only operates on a single input, you can often use the keyword each as a shorthand. each implicitly represents the single argument that would normally be passed into the lambda. Instead of defining a full lambda function with its argument, you can directly use each within an expression, making the overall expression more concise.

Thus, if a function expects a lambda that adds 1 to a number, you can replace this:

LAMBDA(x,x + 1)

with this:

each + 1

In the second example, each represents the input number. 

Examples

To write a simple Lambda consisting of two variables, “foo” and “bar,” that are concatenated together, write the following:

LAMBDA(foo, bar, foo + bar)

This function will not return any data by default, it must be wrapped with a Lambda Helper function. We can use the Apply function to assign values to foo and bar and then execute the Lambda expression:

1. APPLY(LAMBDA(foo + bar, foo, bar),"Hello, ", "Steve"): this expression outputs Hello, Steve

2. APPLY(LAMBDA(foo + bar, foo, bar),source.first_name, source.last_name): this expression outputs the values of the first_name and last_name fields in source data, with no spaces or any other characters separating the two

3. APPLY(LAMBDA(foo + bar, foo, bar),1,2): this expression outputs the number 3 (i.e. the sum of 1 and 2)

4. For a more real-life example, consider a data source that includes a repeated array of line_item objects structured like this: {... "line_items": [ { "sku":"abc-123-a" "id":"18255552209" "quantity":1 "price":"19.95" "discount":"0.00" }, { "sku":"def-123-b" "id":"18255552209" "quantity":1 "price":"12.50" "discount":"2.00" } ] ...}

5. A lambda to return each line_items.sku from the data would be paired with a Get function and written like this: each.sku

Not

Description

1. Returns the opposite of a logical value.

Syntax

NOT(expression)

Examples

1. NOT(TRUE) returns FALSE

2. NOT(FALSE) returns TRUE

Or

Description

1. Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. Will apply to all elements of a list in the first position.

Syntax

OR(logical_expression_1, [logical_expression_2, ...])

1. 0 is logically FALSE

2. All other numbers (including negative numbers) are logically TRUE

Examples

1. OR(false,true) returns TRUE

2. OR(false,false) returns FALSE

3. OR(0,1) returns TRUE

Type

Description

1. Returns the Python type of the value within the function, including: str, int, bool, Decimal, dict.

Syntax

TYPE(value)

Examples

1. TYPE("a") returns str

2. TYPE(TRUE) returns bool

3. TYPE(12.2) returns Decimal

4. TYPE("2022-12-10") returns str

5. TYPE(2022-12-10) returns int (2022-12-10 is interpreted as a subtraction, that value resolves to 2000, which is an integer)

6. TYPE([1,2,3]) returns ['int', 'int', 'int']

X Or

Description

1. Returns TRUE if an odd number of the provided arguments are logically true, and FALSE otherwise

Syntax

XOR(expression, [expression, ...])

Examples

1. XOR(true,false) returns TRUE
2. XOR(true,false,true) returns FALSE