With dynamic pivots in Insights, you can create custom tables tailored to your data needs and visualize data in various formats. The dynamic pivot function is used in the query editor and enables users on non-GXP tenants to group lists into tables by rearranging rows into columns. This article covers what dynamic pivots are, how to use them, and what they are useful for.
Note: Insights is only available to tenants on Enterprise or Industry plans.
What are dynamic pivots?
Dynamic pivots are based on dynamic data sources, with ranges that adjust automatically when data is added or removed. Pivots take slices of that data and spread it out into distinct columns, pivoting how the data is displayed.
If you have a list of items in rows, you can turn that list into a table where one of the attributes is now the column, allowing you to reorganize content in ways that make it easier to find the information you need.
For example, the following table displays data we queried about notebook entries:
We can use the dynamic pivot function to pivot this data by
created_month. Now, it displays the data as a table where user_name defines rows and
created_month defines columns.
Test it out by adding this query to your query editor:
'select u.name user_name, date_trunc(''month'', e.created_at)::date created_month, count(e.id) countof from entry$raw e left join user$raw u on e.creator_id = u.id group by 1,2',
'select distinct date_trunc(''month'', e.created_at)::date from entry$raw e'
Use dynamic pivots
The base function
ARGUMENT) can use several arguments:
The number of columns in your data_query’s output that you want to keep as rows. The unique values of these columns remain as distinct rows in the pivoted output table, as opposed to being turned into columns in the pivoted table. For example, if a
A text argument representing the main query of the underlying data that you want to pivot. The
A text argument representing the query that defines the columns of your pivoted table. The values in the second to last column of your
In the example below, we perform a dynamic pivot to make
created_month a column field instead of a row field and keep
entry_template_id as distinct rows.
Below is the logic used to complete the dynamic pivot function:
number_of_row_fieldsis 2, since we want
entry_template_idto both remain as rows.
data_queryis the query that generated the table above:
‘select u.name user_name, entry_template_id, date_trunc(''month'', e.created_at)::date created_month, count(e.id) countof from aweb.entry$raw e left join aweb.user$raw u on e.creator_id = u.id group by 1,2,3'
Note the following to make this query work:
The query must be used in the function as a string, so it must have single quotes at the start and end of the query.
To make single quotes for strings inside the query work, escape them. In Postgres, you need to use another single quote. For example, if the underlying query has a function like
date_trunc('month', e.created_at), change this to
date_trunc(''month'', e.created_at)so that the string isn’t ended prematurely.
The columns need to match the spec of what the data query is expected to return. Here, since
number_of_row_fieldsis 2, there should be four columns:
Col 1: A row field (
Col 2: A row field (
Col 3: The field that will become columns (
Col 4: The data in the body of the pivoted table (
column_queryis the query that produces the distinct column headers you want. Here, this is
created_monthvalues, so the query is:
'select distinct date_trunc(''month'', e.created_at)::date from aweb.entry$raw e where e.entry_template_id is not null'
Using this logic, the dynamic pivot function is:
'select u.name user_name,
date_trunc(''month'', e.created_at)::date created_month,
from aweb.entry$raw e
left join aweb.user$raw u on e.creator_id = u.id
group by 1,2,3',
'select distinct date_trunc(''month'', e.created_at)::date
from aweb.entry$raw e'
Note: At this time, you can only designate a single column field, so column_query must always return a single column.