transpose
Use the transpose operator to create a pivot table for further use in visualizations, similar to a pivot table in MS Excel.
Syntax
|transpose row=<first_column_field> col=<column_of_field_values>
first_column_field
|
Required. Specify the field to be displayed as the first column in the resulting table. |
column_of_field_values
|
Required. Specify the field whose values will be used as columns in the resulting table. The resulting table supports up to 10 values for columns. If there are more than 10 values, then the rest of them will be grouped in a separate column called |
Usage
You can use the transpose operator to create a cross table that can be displayed in any chart that has two axes.
The values related to the first numeric field, different to the fields specified in row=<field1> and col=<field2>, will be displayed as values in the table.
Examples
Example 1: Transpose rows to columns using state as row field and category as column field.
|transpose row=state col=categoryExample 2: Transpose rows to columns using year as row field and category as column field.
|transpose row=year col=categoryExample 3: Transpose rows to columns using status as row field and year as column field.
|transpose row=status col=yearExample 4: Buckets OCSF events hourly, counts per activity type, then pivots so each activity becomes a column and each row represents an hour.
ocsf | bin event_time span=1h | aggr count by event_time, activity_name | transpose row=event_time col=activity_name