Advanced joining (using conditions in your joins)

As we saw in a previous recipe, the Join operation can be performed with other tools too, not only with the Join tool. I’ll use the joining word to describe the blending operation between datasets, not referring specifically to the Join tool.

But to perform a good Join operation, we must ensure a couple of things.

I’ve seen a lot of tutorials and articles that recommend using the Unique tool for cases where we need to use a lookup table to add additional fields to our dataset. This method is OK when we have the same attributes for the same keys occurring more than once in our lookup table/s. But what happens when we have duplicate occurrences and we need to apply a condition to determine which one to use?

For example, we have a billing dataset with the article code. We need to add the product description to our analysis, so we need to blend our original dataset with another one containing the article’s...