Group two datasets and join them

deona720

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

ggenovese

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.

ggenovese

Yes, in the config for the append tool you can choose the "Include All Columns" option

ggenovese

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.

deona720

@ggenovese Thanks for your response, is it possible to keep the columns that don't match on the new dataset?

ggenovese

Yes, in the config for the append tool you can choose the "Include All Columns" option

deona720

Can't believe I never saw that before, thank you so much @ggenovese!

ggenovese

it's easy to miss! Hope that works for you!

deona720

@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?

ggenovese

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

deona720

@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!