Joining multiple reference columns to a single reference column

[Shaw_16]

Hey All!

I am having issues joining two sets of data together. There is s single point of reference between the two sheets; but while one is consolidated into a single column, the other has the information spread across three columns. 

I've tried different Join and Multi Join configurations but I seem to need three different Joins and a Union to get either partial results or with the Multi-Join I only seem to be getting Union results. 

Below is some sample data the mimics the tables I'm trying to Join - I'm wanting to Join on the "Area" columns.

Multi-Column Reference 

Record ID	Area 1	Area 2	Area 3	Full Name	Address
1			11111	Adam Sample4	123 Street
2		11116		Gus Sample5	223 Street
3			11112	Dave Sample6	323 Street
4			11113	Steve Sample7	423 Blvds
5			11114	Herc Sample8	523 Blvds
6			11115	Medae Sample9	623 Street
7		11117	11118	Janice Sample11	723 Street
8	11119			Nancy Sample3	823 Ave
9	11120			Bill Sample2	923 Ave
10	11121	11122		Zoe Sample1	1023 Ave

Single Column Reference

Area	Unit	Unique Info
11111	1203f4j8	asdlgkfjfg
11116	1m302askdnf0	alskdgjn
11112	1203f4j9	skdfphjm
11113	1m302askdnf1	wpeorit
11114	1203f4j10	sldfjm
11115	1m302askdnf2	sldfmsdf
11118	1203f4j11	alksdjalksj
11117	1m302askdnf3	akdofmpg
11119	1203f4j12	apofdjkg
11120	1m302askdnf4	pzslfdaghl
11122	1203f4j13	apohekj
11121	1m302askdnf5	sipojfnmafds

Let me know if that makes sense or if more clarification is needed 🙂

Attached as well are the two sample data files

[kelsey_kincaid · Accepted solution]

Hi @Shaw_16 ,

It might help to consolidate all of these fields into a single field using Transpose, then join that back with your original dataset. From there you can join on that Area ID.

[Shaw_16]

Hey kayers, 

This would probably work if I had a smaller amount of rows - with the original docs one has nearly 10k lines, the other is 30k - 40k. 

[Shaw_16]

I forgot that Alteryx isn't unwieldy like excel is when moving around large amounts of data!

Tried this out and it solves the issues 🙂