INTERSECTION

INTERSECTION(ignore_blanks, *arrays)

Returns the intersection of one or more arrays, optionally ignoring blank values.

This function performs a set intersection operation across all input arrays. Only elements present in every array are included in the result. Duplicates are removed automatically, and order is not guaranteed. Blank values can be optionally ignored using the ignore_blanks flag.

Parameters:

• ignore_blanks (Operand | bool) – A boolean specifying whether blank values should be ignored in the input arrays.

• *arrays (Operand) – One or more array objects to intersect. All arrays must have the same base data type.

Return type:

Formula

Returns:

A formula object representing the intersection of all input arrays. The output array contains only elements present in every input array, with duplicates removed.

Raises:

• ValueError – If ignore_blanks is not BOOLEAN.

• ValueError – If no arrays are provided.

• ValueError – If any input is not an array type.

• ValueError – If arrays do not all share the same base data type.

Examples

Basic usage with integers:

INTERSECTION(True, [1, 2, 3], [2, 3, 4])
# Returns [2, 3]

Strings with blanks ignored:

INTERSECTION(True, ["A", null, "B"], ["B", "A", null])
# Returns ["A", "B"]

Single array de-duplication:

INTERSECTION(False, [1, 2, 2, 3])
# Returns [1, 2, 3]

``ignore_blanks`` — accepted types

• BOOLEAN

``array`` (variadic) — accepted types

• BOOLEAN_ARRAY

• DATETIME_ARRAY

• DATE_ARRAY

• DECIMAL_ARRAY

• INTEGER_ARRAY

• OBJECT_ARRAY

• STRING_ARRAY

• TIME_ARRAY

Return types

• BOOLEAN_ARRAY

• DATETIME_ARRAY

• DATE_ARRAY

• DECIMAL_ARRAY

• INTEGER_ARRAY

• OBJECT_ARRAY

• STRING_ARRAY

• TIME_ARRAY