I have two datasets, and the other dataset has too much segmented data. I hope to aggregate certain fields, like “Group by” in SQL, and then use these fields as connection keys to connect the two datasets. Otherwise, when connecting two datasets, the datasets will be copied into many copies. For example, Table 1 has name, account, and revenue. Table 2 has names and gms. When two tables are connected, revenue is added to each account using name as the key. (Because there are other fields present, we cannot simply use the average value). I hope the final table format will be: name, revenue (total account), gms. How can I do that？
Hi @wantiqic ,
I think I understand what you are trying to do. Essentially you want to combine both tables by name and show the total revenue by name. I would try this to start:
– For Table 1 (aggregate by name)
SELECT
name,
SUM(revenue) as total_revenue
FROM table1
GROUP BY name
– Then join with Table 2
SELECT
t1.name,
t1.total_revenue as revenue,
t2.gms
FROM (
SELECT name, SUM(revenue) as total_revenue
FROM table1
GROUP BY name
) t1
LEFT JOIN table2 t2 ON t1.name = t2.name
Let me know if this helps!
Thanks! I understand what you mean.But my goal is to visualize the connected data in the ‘analyses’ function of Quick Suite. I think this should be done in the data editing interface of Quick Suite, but there are no aggregation options or code filling places there.
Hi @wantiqic ,
If your goal is to visualize the connected data, then I would recommend you use the new data prep experience. With the new data prep experience there are built in features that are not offered in the old data prep experience like aggregation.
Below is the link on how you can switch to new Data Prep experience. Features not supported in the new data preparation experience - Amazon Quick Suite
Hi @wantiqic ,
We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution" (check box under the reply)