Group two datasets and join them
I have two excel files one includes sales data and line items of products sold in each invoice, the other sheet I have is the freight charges per invoice. I want to be able to sum the freight and sales totals on both sheets and group them by the invoice number on a combined sheet. Is this possible? I sort of started with the group by tiles in each sheet but got confused from there.
This is a same of my sales data: (it might have multiple products or likes per invoice)
Date | Member | Invoice Number | Product | Sale Total |
---|---|---|---|---|
1-Oct | A | 111 | Headphone | 10 |
1-Oct | A | 111 | TV | 50 |
21-Oct | B | 222 | Speaker | 30 |
30-Oct | C | 333 | TV | 50 |
11-Oct | D | 444 | TV | 50 |
11-Oct | D | 444 | Speaker | 30 |
11-Oct | D | 444 | Headphone | 10 |
This is a sample of my freight data: (it will have a freight charge from each month so there are multiple invoices per member on this)
Date | Member | Invoice Number | Freight Charge |
---|---|---|---|
1-Oct | A | 111 | 20 |
1-Mar | A | 111 | 10 |
21-Oct | B | 222 | 30 |
30-Oct | C | 333 | 20 |
11-Oct | D | 444 | 40 |
11-Oct | D | 444 | 50 |
11-Oct | D | 444 | 50 |
This is what I want it to look like: (All grouped by the invoice number which sums the sale total and freight total)
Dale | Member | Invoice Number | Sale Total | Freight Total |
---|---|---|---|---|
1-Oct | A | 111 | 60 | 30 |
21-Oct | B | 222 | 30 | 30 |
30-Oct | C | 333 | 50 | 20 |
11-Oct | D | 444 | 90 | 140 |
Best Answers
-
In Magic ETL you would append these two sources together into a combined output. The append tool will line up your columns by matching names so that you can then build a card off of the dataset that groups by Date, Member, Invoice and SUMs the Sales Amount and the Freight Amount.
0 -
Yes, in the config for the append tool you can choose the "Include All Columns" option
0
Answers
-
In Magic ETL you would append these two sources together into a combined output. The append tool will line up your columns by matching names so that you can then build a card off of the dataset that groups by Date, Member, Invoice and SUMs the Sales Amount and the Freight Amount.
0 -
@ggenovese Thanks for your response, is it possible to keep the columns that don't match on the new dataset?
0 -
Yes, in the config for the append tool you can choose the "Include All Columns" option
0 -
Can't believe I never saw that before, thank you so much @ggenovese!
0 -
it's easy to miss! Hope that works for you!
0 -
@ggenovese Sorry one more question, i appended the data and then tried to make a pivot table using the invoice number and summing those total columns, but as soon as I add in account name next to the invoice number it duplicates everything and wont put freight and sales in the same row and instead separates them into two different rows. Do you know why it would be doing that?
0 -
If Account Name is not common to both datasets that you appended together then that might explain why that's happening, because you'd have a row with the account name and then another row for the NULL account name. Or for that matter if the account names are even slightly different, extra spaces, or if one is upper case and one is lower case this can also cause additional rows to be created
0 -
@ggenovese that seemed to be the case, I ended up just creating a view in analyzer joining one of the data sets back in to pull account name and that worked!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive