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.

  Supported types:

  • BOOLEAN

• *arrays (Operand) –

  One or more array objects to intersect. All arrays must have the same base data type.

  Supported types:

  • INTEGER_ARRAY

  • DECIMAL_ARRAY

  • STRING_ARRAY

  • BOOLEAN_ARRAY

  • DATE_ARRAY

  • DATETIME_ARRAY

  • OBJECT_ARRAY

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.

Supported types:

• INTEGER_ARRAY

• DECIMAL_ARRAY

• STRING_ARRAY

• BOOLEAN_ARRAY

• DATE_ARRAY

• DATETIME_ARRAY

• OBJECT_ARRAY

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]