INDEXBY
Returns a value of a specific index within a column, partitioned by the unique values of a separate column
INDEXBY( column
, integer
, partition column
, [sort column]
, [ascending]
)
Indexes a given column when grouped by unique values within a partition column
INDEXBY() is a window function, which performs like INDEX() except partitioned by the unique values of a different column.
Arguments
Argument
Description
column
Column to index
integer
Index of indexed column
to ouput
partition
column
Column containing unique values
[sort column]
Optional. Column with which to sort partitioned rows by. Defaults to index.
[ascending]
Optional. Enter True
to sort in ascending order (default), or False
to sort in descending order.
Output
Outputs a given index from each partitioned group within the indexed column
Examples
Let's say that we want to find the first state in each region in a table. We have a table that lists each state and its corresponding region -- by using INDEXBY({State}, 1, {Region})
, we can select the first state for whatever region that state is in.
Last updated