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!
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)
Thank you. I’m testing the new experience. But one reason I don’t use it directly is that when uploading an Excel file with multiple sheets, there’s a glitch causing incorrect uploads. I believe this is a bug: for example, when I select sheet1, it actually imports sheet2 instead
There are too many bugs in the new Data Prep experience. For example, when I convert data types, both percentages and numbers with a thousand separator style are judged as strings, and when converted to numbers, they are all treated as null values.
Thanks! After switching to the new experience, aggregation is now possible and the issue has been resolved 
Hi @wantiqic ,
Glad to hear the new data prep experience worked for you! The new data experience is still relatively new so there are a few functions that are still being worked on. This is why they still allow users the ability to switch back to the old data prep experience.