Excluding Where Matched in a Join

Tessa

Hi All,

I have an issue with trying to use the join tool like vlookup.

Here's the issue:

Dataset A contains "Identifier1"

Dataset B contains "Identifier2"

(most of the customers overlap, but their two identifiers, 1 & 2, are different from each other)

Legacy Dataset - contains "Identifier1" and "Identifier2"

I am trying to find "new" customers where they do not match on either of their identifiers in the Legacy Dataset.

I perform a join where I am using both criteria in the join - then I can export the file and do vlookups to see if there are any matches remaining- there are.

How can I get the join to omit where there is any match between the datasets?

Datasets

Join

Accepted answers

danilang

Hi @Tessa

This is one those issues where sample data is extremely useful. I've built something based on what I think your 2 inputs look like, but your statement ""New Customer DB" contains both ID#1 and ID#2" is ambiguous. Does it mean that New Customer DB has one ID column that can contain values from both legacy ID columns, or does New Customer DB have an ID1 and an ID2 column.

This solution assumes the latter, both tables contains an ID1 and and ID2 column

It uses 2 joins and then unions the results and performs a unique on New customer name. This gives you the new customers that match one of 2 legacy ids. This list is joined with the New Customers and the R output is used to give you the New Customers that don't match either.

If this isn't what you're looking for, please provide some sample data that covers all the possible cases and what the output will be. Modify the contents of the the legacy and new customers text inputs and post the workflow back here

Dan

NoMatch.yxmd

All comments

PeterA1

One way I can think of doing this would be to have a Join tool with the legacy dataset in the R input and Dataset A in the L input, then have a second join tool that has Legacy Dataset in R input and Dataset B in the L input, Then you can union the Two L output anchors (unmatched records from A and or you can join them together by some other identifier you have. From there you can filter for the uniques using the Unique tool and output

PeterGoldey

Hi Tessa,

This is a little hard to follow.

Sounds like perhaps matching to the first dataset and taking the unmatched (left) output and then matching against the second and again taking the unmatched output could work.

But its not clear to me if your datasets A and B are both discreet. Or is there a customer_id that they share?

If you can post some sample files or images that would clear things up i think.

Tessa

Oh, how I wish that would work! That's what I was initially trying to do and getting nowhere. I'd still find matches outside, when validating with a vlookup.

I'm just not sure what I am doing wrong here!

Tessa

It's hard even for me to follow! I'll try my best to clarify...

I am trying to find "new customers" based on their IDs and whether they've shown up in our database before - there are two possible IDs that they could have- ID#1 and ID#2. We'll call this "New Customer DB"

In the "legacy" database, each customer has both an ID#1 and an ID#2.

1. "New Customer DB" contains both ID#1 and ID#2

2. Join "New Customer DB" to "Legacy" on ID#1

3. Join "New Customer DB" to "Legacy" on ID#2

A customer can match on one but not the other - so in some instances you may see a customer who doesn't match on ID#1 but does match on ID#2. Same in reverse- match on ID#2 but not on ID#1. Neither of those customers are "new."

After I complete steps 1-3, I need to bring this data together in an additional join; my attempts at this have been fruitless.

I am able to export steps #2 & #3 and find all new customers using 2 vlookups and some filters, but I would like to be able to accomplish that in the workflow....

danilang

Hi @Tessa

This solution assumes the latter, both tables contains an ID1 and and ID2 column

Dan

NoMatch.yxmd

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157