Join Without Duplicates

Question

Hello,

I am very new to Alteryx and am struggling with this task. I need to join two files without duplicating the data for each organization. I can't share the full files for privacy reasons, but any help is appreciated. I really just need all of the columns as they are in the second image, with a new column added that includes the CONVERTED (TB) values for each org. So, ideally, this client should still have 3 rows for each of its orgs. How can I do this?

This is one of the files I want to join (L). One account can have multiple orgs (subsidiary if you will), but each data is needed.

Here is my second set from a different report. I need to add each CONVERTED (TB) value from the first set to the corresponding org/column of the second.

This is what I get when I join by the "SalesForce Name" fields. There are too many duplicates, I just need the 3 rows for each of the orgs with their CONVERTED (TB) value.

I appreciate any and all feedback!

jirikrecek · Answer

@jackyy Jacky, this is what I was mentioning. Look at your column values in the right table - your left table says "ajcdawgnation" in the left table and your right table has "ajc dawgnation".
So, the left table is pulling three rows, but right table has no matching record and backfills it with zeroes.
So you have 2 issues going on at the same time

* you are seeing duplicates, because right table has more than one matching record
* you are seeing no data, because your data in right and left tables is dirty and the values do not 100% match

in my previous post I suggested you look at your data BEFORE You do any joining and be sure to clean it up - you can do this in Excel and just look at your Org names. Looks like your Grafana values always match the Org value, so if you do a join you can purely use SFName + Org column combination as your primary key, but.....
you have to clean up the data first, because the extra spaces in your right table is compromising the join = not finding matching records

If I were doing this clean up, I'd put the right and left table into Excel, merge the SFName and Org columns into a single string and do VLOOKUPs both ways, from left to right and right to left, looking for any #N/A values in the vlooup where it should have found a match, but didn't. You can do the same in Alteryx, but in Excel it is much much faster to spot the data integrity issues.
Your data is dirty, and that is based on my seeing only a dozen records, so I am assuming there is a pervasive problem with the Org column

jackyy · Answer

Hi @jirikrecek, thank you for the detailed suggestions, I appreciate it. I tried to set the unique records for SalesForce Name, Org, and Grafana but the results did not include the values for the rest of my numerical columns (which are needed), how can I avoid this?

jirikrecek · Answer

Here is a live example of your problem:

* Starts with 7 records in left table
* notice how the join's J anchor shows 18 records coming out, after bringing data from right table
* Notice how the U anchor of Unique tool brings it back to 7 original records
* This is why it is a good idea to always use a Unique tool after a join - you never know if the right table will start showing duplicate values for your primary key in the future and while you may not see duplicates now coming out of the join, they may start appearing in the future, without you even touching the workflow. The Unique tool is more of a tollgate to prevent any duplicating of records after a join. For peace of mind.

After Unique tool

Before unique tool and after join

Flow itself

JACKYY JOIN WITH NO DUPLICATES.yxmd