Using dynamic pivots in Insights

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 dynamic_pivot(1,

'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 dynamic_pivot(ARGUMENT) can use several arguments:

In the example below, we perform a dynamic pivot to make created_month a column field instead of a row field and keep user_name or entry_template_id as distinct rows.

Below is the logic used to complete the dynamic pivot function:

• number_of_row_fields is 2, since we want user_name and entry_template_id to both remain as rows.

• data_query is 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_fields is 2, there should be four columns:

    • Col 1: A row field (user_name)

    • Col 2: A row field (entry_template_id)

    • Col 3: The field that will become columns (created_month)

    • Col 4: The data in the body of the pivoted table (countof)

• column_query is the query that produces the distinct column headers you want. Here, this is created_month values, 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 dynamic_pivot(

2,

'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',

'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.