Pivot Table for data exploration

As a data tool, I find myself really needing a pivot table. I am creating like 10 querries and slowing things down considerably just to give the users some semblance of a pivot table.

It would be really great to have something that could pivot data, or at the very least dynamically group data without having to write a billion lines of code.

2 Likes

Hi @szabon ,

did you consider to use a js transformer with a bit of lodash, such as:

{{_.groupBy(query1.data, 'country')}}

Hope this help

Thanks for the request @szabon and the suggestion @abusedmedia! :grinning: We have a ticket submitted for a pivot table component. I'll let you know when there are updates on that!

1 Like

Hi there. I've asked a few times about pivot tables (and written my own js from scratch). However I think a really good interim, possibly permanent solution would be to integrate react-pivottable as a custom component.

However, I've tried, but (a) my development chops are not up to it and (b) I find the custom component help confusing, it's not really clear on what syntax you should use to pass data from the model to component.

Also, it doesn't appear to be available on cdnjs, so not really sure how you include the library.

If anyone (community or ReTool team) fancied setting up an example installation I think that would make a lot of people very happy. :smile:

Can I +1 this. What's the status of this feature?

Thank you for your +1! Will let you know if we have any upcoming plans as soon as I hear back from the teams that would work on this

I did manage to get pivot.js working as a custom component if that's any use, see here:

thx

1 Like

Thank *you* for hopping in with that @domjammoo!

For anyone reading, we’re currently working on a new version of our Table component, so we've added this to our Table roadmap. To share where we’re at, our new Table component currently supports scrolling up to 500K rows and includes 11 of our most popular cell types (Number, Text, DateTime, HTML, etc). It also supports basic column functionality (resizing, reordering, pinning, hiding, data alignment), single column sorting, select row and cell event handlers, theming, and keyboard navigation.

1 Like

Hi Victoria. This is great news. When will the Pivot table function be available? I was able to implement domjammoo's well laid out use of pivottable.js which is great for display but I need the capability of allowing users to edit certain data points in the table. Hoping this feature is right around the corner.

I am shocked that retool has not prioritised a pivot table tbh.

Yes, you can implement your own with a lot of dev work, but all of your competitors have a pivot table available and it makes it very tempting to jump ship. My org is considering it, as without a pivot table real data exploration is being limited in our org.

@Victoria I was able to get a pivot table to display but used Oracle to do it. The next thing after a Pivot Table is available is the ability to then programmatically change editability by column. Of course with the pivot table I now have a set of columns that dynamically change based on the data set. I want to be able to allow the user to edit that data but only based on a prefix of the column. It looks like there is JS to do this but it doesn't work :frowning:

Personally i think there are two things here - editing data in a pivot table can be problematic as often the value in each intersect is an aggregated figure (e.g. SUM) - if the user pivots the table to show subtotals, then it does not make sense to edit them as it cannot be translated to the underlying data.

However, many use cases of a pivot table are not really, 'pivoting', they are just showing data items in a crosstab format with no aggregation.

One idea I had @victoria - many of my use cases are of the second type and all I really want is the columns to represent rows, and the rows to represent values - so for instance you can have a table with one column per customer, where the first row is customer name, and all of their details below.

I wonder how much dev work it would be to add a 'transpose' checkbox to the table component? :slight_smile:

Just a thought....

@domjammoo point well taken. Crosstab format with no aggregation is EXACTLY what I was hoping to accomplish.

My use case is simply trying to plan out hours by person by project by month. Displaying the data as you would store it in a database is NOT visually user friendly. This is where that crosstab functionality would do the trick. I was dynamically pivoting the table in Oracle and displaying it in a Retool table which visually works but you can't edit it because the columns are dynamic. You can't programmatically set the column as editable in Retool so it's display only. I dunno, every way I spin this I run into some roadblock and the majority of them are in Retool... #frustrating. :frowning:

If this was just one project with one performance period it would be easy. Of course, it's not, it's hundreds of projects with multiple performance periods.

Oh well, thank you for helping me figure out I don't want a pivottable :smiley:

Yeah I'm not sure it's just a retool problem. Every javascript component library has a table component, but none of them allow you to use a data attribute as a dynamic column, and a data attribute for the rows. Effectively it's a table with dynamic values on both the x and y axes:

image

And I'm not sure there are any standard components out there which provide this feature. There are a number of 'dynamic pivot table' hacks on the forum which go some way there.

Anyway - I had an idea - given the that time periods are by definition static, I just did a prototype of an approach which is definitely going to solve some of my use case problems - interested to know if it solves your issue:

@victoria please implement a pivot table. this is crucial for a number of management reports.

Hi @ryanrunchey! Thanks for chiming in. I sent your feedback to our team & bumped the request internally :slightly_smiling_face:

1 Like

I’m beginning to think this isn’t actually ever going to happen

Hi there,

Unfortunately, this isn't on our immediate roadmap yet :disappointed:, but it is still in our queue!

Bump and +10000000....

Without a way to pivot the data dynamically in the DB, this becomes an absolute nightmare if the columns need to be dynamic. The workarounds are convoluted and difficult to follow; this should really be prioritized, either through enabling something in the Retool DB or (preferably) through the UI.