Using data field in one dataset to join to multiple columns in another dataset.

Question

In theory this seems like it would be an easy solution using a multi join tool, however, it is not giving me the output I want. Which makes me think this is not as simple as I think.

In the sample dataset, I have two sheets labeled sample 1 and sample 2. Sample 1 is the parent file that I want to keep, and I just want to bring in the Color field from sample 2. The desired output is to have the color join to multiple columns, e.g. Person A's color and Person B's color. Please help me think through the best logic. TY.

Alteryx Join test.xlsx

Annotation 2023-10-18 142751.png

Prometheus · Answer

@mlim0806 The values in sample 2 that are in sample 1 (see Join 1) are Person A ID (1 and 2), so the output of that first Join is two records. The third record from sample 1 and the other 7 records from sample 2 exit Join 1 at the L and R output anchors, respectively. I then took those outputs and joined them again (Join 2) on Person A --> Person B. Then I unioned them back together to create the output that has the original 3 records from sample 1 with their corresponding colors.

Joining.yxzp