Joining tables without repetitive values

Question

Hello community,

I'm trying to work on a problem where I want to join two tables on multiple primary fields such that the values are not repeating after the join. For example, in the sample workflow attached, I have two input files having id, name, state, account, month-year, year, and sales as their columns. If I join the two tables on id, state, and account, the records are getting duplicated from the left table for each sales value in the right table, specifically for the names that are common in both the tables (For example, Jim and Erica). However, I want my output in this format such that there are no duplication of records:

IdNameStateAccountMonth-YearSalesRight_Month-YearSales 23ChuckPA192022-03362022-03353ChuckPA19 362021-01173ChuckPA19 362021-03193ChuckPA19 362021-07223ChuckPA19 362021-08243ChuckPA19 362022-01273ChuckPA19 362022-02303ChuckPA19 362022-04323ChuckPA1122022-04  415JimTX872021-0154  5JimTX872021-0234  5JimTX872021-0325  5JimTX872021-0432  5JimTX872021-0524  5JimTX872021-0627  5JimTX872021-0729  5JimTX872021-0938  5JimTX872021-1042  5JimTX872021-1143  5JimTX872021-1249  5JimTX872022-01502022-01435JimTX872022-02562022-02565JimTX872022-03572022-03355JimTX872022-04592022-04445JimTX872022-0562 437EricaMN722021-0615 597EricaMN722021-0916 597EricaMN722022-01192022-01597EricaMN722022-0220  7EricaMN722022-0322  7EricaMN722022-04232022-04707EricaMN722022-0524  11HeatherUT332022-01   11HeatherUT332022-02   12AmaraNV492022-04   12AmaraNV922022-01   12AmaraNV922022-02   12AmaraNV1702022-02   12AmaraNV1702022-03

Can someone please help me how to approach this problem?

Sample Join.yxmd

MichelleL · Answer

@keeprollin  - I added 'Month-Year' to the Join fields, then brought the Left side output into the Union, and finally added a sort.  The repeating values are gone!

Solution attached.  Please mark this solution as 'accepted' if it works for you!  Or let me know if you need more help :-)

Sample Join_Solution.yxmd

IraWatt · Answer

Hey @keeprollin,

What logic do you want to use to remove duplicate records. For instance, Erica has these three:

7EricaMN722021-0615 597EricaMN722021-0916 597EricaMN722022-01192022-0159

Should we just take the first record? Or do we need to sum the sale or anything like that?

If you just want the first record you can use the sample tool before hand:

This will ensure there is only 1 id, state and account number to join on.

Any questions or issues please ask :)
HTH!
Ira

Sample Join.yxmd