Join with key value that appears multiple times

[Ben_Naylor]

Right now, I have two different data sets that I am trying to recombine. One has a multitude of different columns one of which is a concatenation of two of the columns in the data set. The other data set has the same concatenation , some other columns and a column called entry type. All that I want to do is combine the two data sets based on the concatenation column so that I essentially just add on the entry type column to the first data set. However the issue that I'm having is that in the first data set, the concatenation appears multiple times while the second data set is grouped by them, so each concatenation only appears once. Essentially, this is just a VLOOKUP but i'm not sure how to do that in Alteryx. Any and all help is immensely appreciated!

[myastarling · Accepted solution]

Hi Ben,

You could use a join tool with a subsequent union tool to create a left join -- I've attached an example. Would this accomplish what you are looking for?

[Ben_Naylor]

Yes! That's exactly what I needed. Just so I know for the future, what exactly is going on there? I see that it works but am not 100% sure why it does.

[myastarling]

The join tool will only do a simple join -- so it will either do all of the items that have the specified fields match (J), the items that are in the first table but not the second (L), or the items that are in the second table but not the first (R).

For the sample data that I included, just the join tool itself will work (click on the join tool, hit control-shift-B to add browse tools to all outputs -- you will see all the joined items in the J output)

But as far as a VLOOKUP goes, I'm assuming you'd also want all of the output from the first table that didn't join as well as the output that did -- that's where the union comes in. It is taking all of the L output and appending all of the J output to it as well.

If you tack browse tools onto each tool, you can see what is going on with each step -- that's one of the things I totally love about Alteryx is being able to view how the data are being transformed with each tool 🙂

As an aside, if you are concatenating the data first to be able to duplicate the functionality of a VLOOKUP, you don't need to do that at all -- what you can do instead is join by the unconcatenated fields in the join tool.

I've attached another workflow showing this, that also includes an unmatched item in list 1 to show you a little better what the join and union tools are doing in this case.