After joining two tables by account number, I am getting duplication since each source has a different number of rows. Here is the account table, with multiple cities and city sales per account:
This table is then joined to the Sponsor table, with multiple sponsors and sponsor sales per Account:
The resulting view has duplicate Cities and duplicate sponsors due to the join:
What I really want is this:
Where each account has X rows, where X is the maximum # of cities or sponsors. The cities and sponsors should be side-by-side, as should be the sales, with no duplication, and blanks where the # rows do not match. The account-level dimensions, Ship and Level, should be the same for all rows in the account.
I hope this is possible, I am 100% open to using Alteryx or Tableau Prep if absolutely necessary.
Here is how it would look in Excel with repeating cells:
and here are correct Account Only and Sponsor Only views:
.TWBX file attached below:
Any help or direction would be greatly appreciated.
Thank you.
Well, so that's why you're getting duplicates. When there's no logic for matching one row with another row, relationships or joins will give you all possible combinations, so if an account has 3 cities and 4 sponsors, you'll get the 12 combinations.
The solution I'd suggest you is to create an arbitraty logic, using Prep.
I suppose that there is a maximum of 10 cities or 10 sponsors per account, but if it's more you'll just have to adapt.
- With Excel or any text editor, you create a 'Numbers' file with a column named Number that includes numbers from 1 to 10.
- You include this file in Prep, I'll call it N.
- You include your Accounts table in Prep, I'll call it A1.
- You include your Accounts table a second time (I'll call it A2), and you add a city number restarting at 1 for each account (I think the last version of Prep has added functions like JOIN, RANK, RUNNING_COUNT and WINDOW_COUNT).
- You include your Sponsors table, and you add a sponsor number restarting at 1 for each account. This will be S.
- You cross join N and A1. Let's call this X.
- You left join X to A2 on A1..Account = A2.Account and N.Number = A2.City Number. Let's call this XA.
- You left join XA to S on XA.Account = S.Account and XA.Number = S.Number. That's XAS.
- You filter XAS on City IS NOT NULL OR Sponsor IS NOT NULL.
Then you should have the data source you need.
---
Antoine Dinimant
Freelance consultant, trainer & teacher