How to Handle Data Transformations in Retool

1// Transformer: processedOrders
2// Input: query1.data = [{ id, user_id, amount, status, created_at, items: [...] }]
3
4const orders = query1.data || [];
5
6return orders
7  // 1. Filter: only completed orders
8  .filter(order => order.status === 'completed')
9  // 2. Map: reshape and add computed fields
10  .map(order => ({
11    id: order.id,
12    userId: order.user_id,
13    amount: Number(order.amount || 0),
14    amountFormatted: Number(order.amount || 0).toLocaleString('en-US', {
15      style: 'currency', currency: 'USD'
16    }),
17    itemCount: Array.isArray(order.items) ? order.items.length : 0,
18    createdDate: moment(order.created_at).format('MMM D, YYYY'),
19    dayOfWeek: moment(order.created_at).format('dddd')
20  }))
21  // 3. Sort by amount descending
22  .sort((a, b) => b.amount - a.amount);

1// Transformer: usersWithStats
2// query1.data = [{ id, name, email, department }]
3// query2.data = [{ user_id, total_orders, total_revenue, avg_rating }]
4
5const users = query1.data || [];
6const stats = query2.data || [];
7
8// Build lookup map: O(n)
9const statsMap = stats.reduce((map, stat) => {
10  map[stat.user_id] = stat;
11  return map;
12}, {});
13// OR with lodash: const statsMap = _.keyBy(stats, 'user_id');
14
15// Join: O(m)
16return users.map(user => {
17  const userStats = statsMap[user.id] || {};
18  return {
19    ...user,
20    totalOrders: userStats.total_orders ?? 0,
21    totalRevenue: Number(userStats.total_revenue ?? 0),
22    avgRating: Number(userStats.avg_rating ?? 0).toFixed(1),
23    isHighValue: (userStats.total_revenue ?? 0) >= 1000
24  };
25});

1// Transformer: orderStats
2// Computes summary statistics for a dashboard
3
4const orders = query1.data || [];
5
6if (orders.length === 0) {
7  return { total: 0, count: 0, average: 0, maxOrder: 0 };
8}
9
10const stats = orders.reduce((acc, order) => {
11  const amount = Number(order.amount || 0);
12  return {
13    total: acc.total + amount,
14    count: acc.count + 1,
15    maxOrder: Math.max(acc.maxOrder, amount),
16    completedCount: acc.completedCount + (order.status === 'completed' ? 1 : 0),
17    pendingCount: acc.pendingCount + (order.status === 'pending' ? 1 : 0)
18  };
19}, { total: 0, count: 0, maxOrder: 0, completedCount: 0, pendingCount: 0 });
20
21return {
22  ...stats,
23  average: stats.count > 0 ? stats.total / stats.count : 0,
24  completionRate: stats.count > 0
25    ? (stats.completedCount / stats.count * 100).toFixed(1) + '%'
26    : '0%'
27};
28
29// Access in components:
30// {{ orderStats.value.total.toLocaleString('en-US', {style:'currency',currency:'USD'}) }}
31// {{ orderStats.value.completionRate }}

1// Transformer: ordersByDepartment
2// Groups users by department for a grouped table
3
4const users = query1.data || [];
5
6// Group by department:
7const grouped = _.groupBy(users, 'department');
8// Result: { Engineering: [...], Sales: [...], Marketing: [...] }
9
10// Convert to array of group objects with summary stats:
11return Object.entries(grouped)
12  .map(([department, members]) => ({
13    department: department || 'Unassigned',
14    memberCount: members.length,
15    activeCount: members.filter(m => m.is_active).length,
16    totalRevenue: members.reduce((sum, m) => sum + Number(m.revenue || 0), 0),
17    members: members.map(m => m.name).join(', ')
18  }))
19  .sort((a, b) => b.totalRevenue - a.totalRevenue);
20
21// For a chart: bind to bar chart with department on x, totalRevenue on y

1// Transformer: chartData
2// query1.data = [{ month: 'Jan', year: 2024 }, { month: 'Feb' }, ...]
3// query2.data = [{ revenue: 12450 }, { revenue: 18200 }, ...]
4// Both arrays are same length, ordered identically
5
6const months = query1.data || [];
7const revenues = query2.data || [];
8
9// Merge parallel arrays:
10return _.zipWith(months, revenues, (month, rev) => ({
11  label: `${month.month} ${month.year}`,
12  revenue: Number(rev?.revenue || 0),
13  revenueFormatted: Number(rev?.revenue || 0).toLocaleString('en-US', {
14    style: 'currency', currency: 'USD', minimumFractionDigits: 0
15  })
16}));
17
18// Alternative without Lodash:
19// return months.map((month, i) => ({
20//   label: month.month + ' ' + month.year,
21//   revenue: Number(revenues[i]?.revenue || 0)
22// }));

1// Transformer: cumulativeRevenue
2// Adds a running total to each row
3
4const salesByDay = query1.data || [];
5
6let runningTotal = 0;
7
8return salesByDay.map(day => {
9  runningTotal += Number(day.revenue || 0);
10  return {
11    ...day,
12    dailyRevenue: Number(day.revenue || 0),
13    cumulativeRevenue: runningTotal,
14    cumulativeFormatted: runningTotal.toLocaleString('en-US', {
15      style: 'currency', currency: 'USD', minimumFractionDigits: 0
16    })
17  };
18});
19
20// For a Chart with two series:
21// Series 1: x = date, y = dailyRevenue (bar)
22// Series 2: x = date, y = cumulativeRevenue (line)

1// Deduplicate by id (keep first occurrence):
2return _.uniqBy(query1.data || [], 'id');
3
4// OR merge duplicates by summing amounts:
5const rows = query1.data || [];
6const merged = rows.reduce((acc, row) => {
7  const existing = acc.find(r => r.user_id === row.user_id);
8  if (existing) {
9    // Merge: add amounts
10    existing.total_amount = (existing.total_amount || 0) + Number(row.amount || 0);
11    existing.order_count = (existing.order_count || 0) + 1;
12  } else {
13    acc.push({
14      user_id: row.user_id,
15      name: row.name,
16      total_amount: Number(row.amount || 0),
17      order_count: 1
18    });
19  }
20  return acc;
21}, []);
22
23return merged.sort((a, b) => b.total_amount - a.total_amount);

1// Transformer: dashboardData
2// Aggregates data from 3 queries for dashboard display
3// query1: users, query2: orders, query3: products
4
5const users = query1.data || [];
6const orders = query2.data || [];
7const products = query3.data || [];
8
9// KPIs
10const totalRevenue = orders.reduce((s, o) => s + Number(o.amount || 0), 0);
11const activeUsers = users.filter(u => u.is_active).length;
12const pendingOrders = orders.filter(o => o.status === 'pending').length;
13
14// Chart data: revenue by month
15const revenueByMonth = _.chain(orders)
16  .groupBy(o => moment(o.created_at).format('YYYY-MM'))
17  .map((monthOrders, month) => ({
18    month,
19    revenue: monthOrders.reduce((s, o) => s + Number(o.amount || 0), 0)
20  }))
21  .orderBy('month')
22  .value();
23
24// Top products by order frequency
25const topProducts = _.chain(orders)
26  .flatMap(o => o.items || [])
27  .groupBy('product_id')
28  .map((items, productId) => ({
29    productId,
30    count: items.length,
31    product: products.find(p => p.id === productId)
32  }))
33  .orderBy('count', 'desc')
34  .take(5)
35  .value();
36
37return { totalRevenue, activeUsers, pendingOrders, revenueByMonth, topProducts };