Pivot matrix table

UI Bakery Roadmap 🏗️
1

Having pivot matrix/table in UI Bakery that allows me to define both X and Y keys and their corresponding fields in a 2-dimensional structure. The matrix should dynamically compute the value for each intersection based on a list of records with two parameters, similar to the approach used in SQL pivot queries. However, unlike SQL, this pivot should be flexible and not require predefined column definitions, enabling dynamic adjustments. This has plenty of use cases, as there are many occasions where you need to display cross-parameter values, such as KPIs for each combination of channel and provider, etc.

2

Hi Diego, you can achieve such behaviour with JS code step.

Let’s assume your db query returned the following data:

[
  { channel: 'Email', provider: 'AWS', value: 120 },
  { channel: 'Email', provider: 'SendGrid', value: 90 },
  { channel: 'SMS', provider: 'AWS', value: 150 },
  ...
]

You want to pivot it into a matrix like:

Channel AWS SendGrid
Email 120 90
SMS 150 null

Here is the JS step which does the trick:

function pivotData(data, rowKey, colKey, valueKey) {
  const rows = new Set();
  const cols = new Set();

  // Collect all unique row and column keys
  data.forEach(item => {
    rows.add(item[rowKey]);
    cols.add(item[colKey]);
  });

  const sortedCols = Array.from(cols);
  const pivot = [];

  rows.forEach(rowVal => {
    const row = { [rowKey]: rowVal };
    sortedCols.forEach(colVal => {
      const match = data.find(item => item[rowKey] === rowVal && item[colKey] === colVal);
      row[colVal] = match ? match[valueKey] : null;
    });
    pivot.push(row);
  });

  return pivot;
}

// Example usage
const records = {{ data }}; // e.g., your db query response
const pivot = pivotData(records, 'channel', 'provider', 'value');
return pivot;