Array
Array functions operate on multiple values stored in an array to process, transform, or analyze the data and return a result.
The following functions are available in Qlik Open Lakehouse:
| Function | Description |
|---|---|
| ARRAY_DISTINCT | Return all the distinct elements in the array. |
| ARRAY_JOIN | This function concatenates the elements of the given array to a string using the separator. |
| ARRAY_MAX | Return the maximum value in an array. |
| ARRAY_MIN | Return the minimum value in an array. |
| ARRAY_SORT | Returns the values in the array in ascending order |
| ARRAY_SORT_DESC | Returns the values in the array in descending order |
| ARRAY_SUM | This function calculates the sum of all non-null elements of the input. If there are no non-null elements, returns 0. The behavior is similar to aggregate function SUM, but operates on an array. |
| COUNT_VALUES | Returns the amount of non-null items in a given array. |
| COUNT_VALUES_IF | Returns the amount of `true` values in a given array. This can be used with conditional operators to count the number of array elements that fulfil a condition. For example, `COUNT_VALUES_IF(my_values[] > 3)` will return the number of elements that are greater than 3. |
| ELEMENT_AT | This function returns an element of an array at a given index. If index >= 0, this function provides the same functionality as the SQL-standard subscript operator ([]). If index < 0, `ELEMENT_AT` accesses elements from the last to the first. |
| FIRST_ELEMENT | Returns the first element in the array. |
| LAST_ELEMENT | Returns the last element in the array. |
| VALUE_INDEX_IN_ARRAY | Calculates a 1-based positional index for each non-null value within a sub-array, resetting the index with each new sub-array. |
| VALUE_INDEX_IN_ROW | Calculates a continuous, 1-based positional index for each value in an array, treating the entire row as a single context. It assigns a unique index to each non-null element across the arrays within the row. |