Logical Functions

Table of Contents

1. And Function

2. Apply Function

3. Boolean Function

4. Coalesce Function

5. Filter Function

6. Identity Function

7. If Function

8. If Error Function

9. Lambda Function

10. Not Function

11. Or Function

12. Type Function

13. X Or Function

Logical 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

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 function.

Syntax

each

Examples

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

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

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

   2. 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

   3. 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

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

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.

SyntaxFILTER(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

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

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

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:
   1. Apply
   2. Filter
   3. Flatmap
   4. List
   5. Map
2. Please note that any function can be used in conjunction with a Lambda function, not just Lambda Helper Functions.

Syntax

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

Examples

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

1. 1. 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`NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`.

Syntax

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

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

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

Examples

1. XOR(true,false) returns TRUE

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