Creating alternative to Pivot Table

App Building
1

Hi all and thanks for any help in advance. We're struggling with displaying data in a different format within a table, functioning much like a pivot table.

I've thoroughly reviewed other discussions on this topic but have not been able to resolve this situation. I'll note here that I am not a developer and rely heavily on GPT and Google for coding.

Below is the query we're trying to display. The new table is meant to have different Channels as column headers across the top of the table (which can be pulled from a getChannels query with Leads data being shown in the table for that channel for each month. We would then have another query that showed a breakdown of New Customers for each month by Channel and so on for the other data like CAC, CPL, etc.

WITH Leads AS (
SELECT
Date,
Channel,
SUM(Total) AS Leads
FROM
ProjectID.RS Monthly Metrics
WHERE
Metric Type = 'Leads'
AND Date BETWEEN {{dateRange5.value.start}} AND {{dateRange5.value.end}}
AND Company = "Client Name"
GROUP BY
Date,
Channel
),
NewCustomers AS (
SELECT
Date,
Channel,
SUM(Total) AS New_Customers
FROM
ProjectID.RS Monthly Metrics
WHERE
Metric Type = 'Customers'
AND Date BETWEEN {{dateRange5.value.start}} AND {{dateRange5.value.end}}
AND Company = "Client Name"
GROUP BY
Date,
Channel
),
Expenses AS (
SELECT
Date,
Company,
Category,
Channel,
Channel Group,
Group,
Line Item,
Location,
Total
FROM
ProjectID.RS Expenses
WHERE
Company = "Client Name"
)
SELECT
Leads.Date,
Leads.Channel,
Leads.Leads,
NewCustomers.New_Customers,
CASE
WHEN Leads.Leads != 0 THEN NewCustomers.New_Customers / Leads.Leads
ELSE 0
END AS Conv %,
Expenses.Category,
Expenses.Channel Group,
Expenses.Group,
Expenses.Line Item,
Expenses.Location,
Expenses.Total AS Expense_Total,
CASE
WHEN Leads.Leads != 0 THEN Expenses.Total / Leads.Leads
ELSE 0
END AS CPL,
CASE
WHEN NewCustomers.New_Customers != 0 THEN Expenses.Total / NewCustomers.New_Customers
ELSE 0
END AS CAC
FROM
Leads
LEFT JOIN
NewCustomers
ON
Leads.Date = NewCustomers.Date
AND Leads.Channel = NewCustomers.Channel
LEFT JOIN
Expenses
ON
Leads.Date = Expenses.Date
AND Leads.Channel = Expenses.Channel
AND "Client Name" = Expenses.Company
ORDER BY
Date DESC

2

Hi @Justin_Fuqua, welcome to the forum! :wave:
It's hard to know what the issue is from just looking at an extensive SQL query. Could you share the table schemas and what we are trying to extract from them instead?

3

Thanks for checking in Paulo. I was able to resolve this issue with a transfomer below and 2 different queries that pull channel names and lead data.

const channelsData = {{ getChannels.data.Channel }}; // Ensure this matches the query name
const leadsData = {{ getLeads.data }}; // Ensure this matches the getLeads query name

// Convert the object to an array of channel names
const channelNames = Object.values(channelsData);

// Check if channelNames is indeed an array
if (!Array.isArray(channelNames)) {
throw new Error("Expected an array of channel names");
}

// Extract and check if leadsData is an array
const months = Object.values(leadsData.Month);
const channels = Object.values(leadsData.Channel);
const leads = Object.values(leadsData.Leads);

// Initialize a dictionary to hold the data by month
const monthData = {};
const channelTotals = {};

// Populate monthData with the leads information
months.forEach((month, index) => {
const channel = channels[index];
const leadCount = Number(leads[index]); // Ensure leadCount is a number

// Initialize the month entry if it doesn't exist
if (!monthData[month]) {
monthData[month] = channelNames.reduce((acc, channel) => {
acc[channel] = 0; // Initialize each channel header with zero
return acc;
}, { Month: month }); // Include the month in the entry
}

// Assign leads to the appropriate channel and accumulate totals
monthData[month][channel] = leadCount;
channelTotals[channel] = (channelTotals[channel] || 0) + leadCount;
});

// Sort channels by their total leads in descending order using channelTotals
const sortedChannels = channelNames.sort((a, b) => (channelTotals[b] || 0) - (channelTotals[a] || 0));

// Create summary row for sorting but exclude from display
const summaryRow = sortedChannels.reduce((acc, channel) => {
acc[channel] = channelTotals[channel] || 0;
return acc;
}, { Month: 'Summary' });

// Convert monthData dictionary to an array of objects for the table
const tableData = Object.values(monthData);

// Ensure the table data columns are in the sorted order of channels
const sortedTableData = tableData.map(row => {
const sortedRow = { Month: row.Month };
sortedChannels.forEach(channel => {
sortedRow[channel] = Number(row[channel]) || 0; // Ensure values are numbers
});
return sortedRow;
});

// Optionally, add the summary row to the table data but mark it for hiding
// Add a special property to identify the summary row
summaryRow._isSummary = true;
sortedTableData.push(summaryRow);

return sortedTableData;

1 Like