Combining separate invoices based on common Order #

[egspen2]

I have a vendor that sends me two separate invoices for the same order. One invoice includes the cost of goods, the other invoice includes the related shipping costs. I'd like to use Alteryx to combine the two Excel invoice details with the common field between the two being the order number. Further, I'd like to filter the combined file into two datasets: 1) order numbers where I've been invoiced for both the cost of goods AND shipping and 2) order numbers where I've only been invoiced for the cost of goods and NOT shipping (indicating I should still be expecting to receive a related shipping bill).

Screenshot of COGS detail:

Screenshot of shipping detail:

Screenshot of current workflow - the filter tool is where I'm stuck, however, open to completely different workflows as well.

[cpapaioannou]

The simplest approach is to use Join tool in order to MATCH each order no. of the COGS dataset with the respective one of the Shipping dataset.

I assumed that the COGS file can contain multiple items per order (duplicate order number) whereas Shipping dataset has only unique order numbers  and I created a very simple WF to further my approach.

You can mark the answer as a solution if it helps you or get back with anything further.

[egspen2]

There are actually duplicate order #'s in both the shipping and COGS files.

Shipping has separate lines / charges for outbound and inbound shipping (for example, $1.00 for order #1234 outbound shipping and $2.00 for the same order #1234 inbound shipping show up as separate lines in the shipping detail).

Similarly, COGS has duplicate lines in instances where a customer has ordered two different products in the same order (for example, order #1234 has one line for the purchase of item 5678 - say $5.00 - and another line item - still same order # - for the purchase of item 3456 - say $10.00).

Using my example order #1234, I'd like to capture the 4 lines that make up the total charges of $18 for that order in my final output (and the same for all orders, of course).

I've made a slight change to my original workflow pictured below as I realized the COGS data has "blank" ($0) shipping lines that are already captured in the shipping dataset. Thank you for any thoughts.

[SeanAdams]

hey @egspen2 

Given that you want to stack the data - what you want to do is use a Union rather than a Join.

- Use a select to make the fields consistent across the two sets

- Union them together (union tool under Join)

- Sort by the order number (Sort tool,  under Preparation)

- Summarize if needed to get to a total (Summarize tool under Transform)