A Dynamic Pivot Table Generator

Hey there!

When tasked with creating a dynamic pivot table app, I didn't really want to rely on external tools and instead thought to craft something with the recently rolled out dynamic columns feature within tables.

I crafted a query to grab grouped sales data from our internal Postgres server and then used Javascript to transform it into an array of grouped objects with month fields and a grant total at the end.

var data = [];
switch({{select1.value}}) {
  case "YYYY-MM-DD":
    data = {{pullDay.data}};
    break;
  case "YYYY-MM":
    data = {{pullMonth.data}};
    break;
  case "YYYY":
    data = {{pullYear.data}};
    break;
}
//const data = {{pullMonth.data}}

//Generate the template for all fields
const templatePreOBJ = [["name", 0]];
for (let item in data) {
  templatePreOBJ.push([data[item].Date, 0])
}
var templateOBJ = Object.fromEntries(templatePreOBJ)

//Process data and build grouped product structures.
var outputArray = [];
data.forEach((item) => {
  if (!(outputArray.map((e) => e.name).includes(item.name_code))) {
    var product = {...templateOBJ};
    product.name = item.name_code
    product[item.Date] = parseInt(item.Count)
    product.grandTotal = parseInt(item.Count);
    outputArray.push(product)
  }
  else if (outputArray.map((e) => e.name).includes(item.name_code)) {
    let index = outputArray.findIndex((e) => e.name == item.name_code)
    outputArray[index][item.Date] = parseInt(item.Count)
    outputArray[index].grandTotal = outputArray[index].grandTotal + parseInt(item.Count)
  }
})

return outputArray

Hope this helps some others who have been wanting dynamic pivot tables!

2 Likes

Realised I made a typo!
*grand

1 Like

This is really cool stuff, thanks for sharing @weeks.

I haven't explored dynamic columns yet, but this gives me a good idea of a use case for them.

Would you mind sharing your dynamic columns settings within the table component? Just curious how it looks like

Hey there Miguel,

All I did to get it working was allow for the columns to get named correctly.

Thanks for your kind words, definitely explore dynamic columns!

1 Like

Hey @weeks,

Just to say I just implemented this in an app and it is working beautifully. Thanks again!