Joining two data sets with different data joined by one field

[Savannah]

Hi Community!

I'm trying to join two data sets with clients A-C in one set and clients A-F in the other using MONTH as common field. I tried the Join tool followed by the Union tool, but this comes back with double entries since the number of unique clients are different in the two data sets. I've attached a mock up and screen shots. 

Desired final result:

Sales under $5

Sales over $5

Any ideas welcome! Many thanks! 

[atcodedog05 · Accepted solution]

Hi @Savannah 

Here is how you can do it. It is advisable to have join key pair as unique combo.

Workflow:

1. I am first using a summarize tool to sum up USD for each month and each client for under 5 USD and over 5 USD. This creates Month & Client unique pair.

2. Using multi join tool outer joining on Month & Client.

3. Using formula tool to fill month and client to create single reference columns.

Hope this helps 🙂

[Savannah]

Hi @atcodedog05 

Not only perfect solution, but Speedy Gonzalez too! 

Many thanks it was just what I was looking for! Fantastic! 🙂

[atcodedog05]

Happy to help @Savannah 🙂

[Vaithi]

Hi,

Added Few more steps to sort the  Month Paid in by Jan to Dec. Hope this helps 🙂

[Savannah]

Ah, terrific! Many thanks!