How do I group fields in a table?

I have a Google Sheets Resource that has data like

Store Date Products Sold
North 2019-01-20 3
North 2019-01-21 5
South 2019-03-22 7
West 2019-02-20 1

And I want to generate a table like:

Store Month Products Sold
North January 8
South March 7
West February 1

How can I generate this table? I have to group by Store, Month and sum the Products Sold.

1 Like

Hey @Alejandro, and welcome to the community! Sorry for the delay, we're usually quicker :slight_smile:

You can do this in Retool via Javascript, but it's going to be kind of gnarly - I'd recommend you actually do this in Google Sheets (perhaps in a separate sheet, so you can create a separate query) and then import into Retool. You'd need to either write a custom JS function (see here) or use Lodash's grouping functionality.

Don’t worry, thanks for the feedback what I actually did was:

On the Google Sheets Query (rawQuery) I added a transformer that helps me with making the transformation:

data.forEach((element) => {
  element["Month"] = moment(element["Date"], "YYYY-MM-DD").format("MMM");
  element["Products_Sold"] = Number(element["Products_Sold"])
return data;

Then I made another query of type Query JSON with SQL that looks like this:

SELECT Store, Month, SUM(Products_Sold) as Products_Sold
FROM {{ }}
GROUP BY Store, Month
1 Like

Niiiiiiiice that’s a clever solution. Glad you were able to get it working and let us know if there’s anything else we can help with!