Skip to main content

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:

 

Thorny duplication issue after joining tables 

This table is then joined to the Sponsor table, with multiple sponsors and sponsor sales per Account:

Sponsor tableThe resulting view has duplicate Cities and duplicate sponsors due to the join:

 

Combined view with duplicatesWhat I really want is this:

 

Desired View 

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:

 

Desired Result in exceland here are correct Account Only and Sponsor Only views:

 

Account Only 

image.png.TWBX file attached below:

 

Any help or direction would be greatly appreciated.

 

Thank you.

11 risposte
  1. 26 giu 2023, 22:32

    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.

    1. With Excel or any text editor, you create a 'Numbers' file with a column named Number that includes numbers from 1 to 10.
    2. You include this file in Prep, I'll call it N.
    3. You include your Accounts table in Prep, I'll call it A1.
    4. 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).
    5. You include your Sponsors table, and you add a sponsor number restarting at 1 for each account. This will be S.
    6. You cross join N and A1. Let's call this X.
    7. You left join X to A2 on A1..Account = A2.Account and N.Number = A2.City Number. Let's call this XA.
    8. You left join XA to S on XA.Account = S.Account and XA.Number = S.Number. That's XAS.
    9. 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