Joining multiple columns with one reference column

[DigitalPlumberDave]

Hi everyone,

I am fairly new to Alteryx - this is my first post - and I need some help joining the below fact and reference tables to produce the third result table:

I can produce the result table by joining the fact table to the reference table twice, but was hoping for a more eloquent solution, if available, as the rest of my workflow is already quite big and complex.

Fact table

Course_Number	Lecturer_Code	Tutor_Code
1	BB01	GG02
2	MM05	Null
3	PP04	WW03
4	BB01	WW03
5	SS06	FF08

Reference table

Educator_Code	Educator_Name	Educator_Position	Educator_Specialty	Tenure
BB01	Barry Brown	Lecturer	Macro Economics	10
GG02	Gary Gold	Tutor	Economics	2
MM05	Mary Matthews	Lecturer	Accounting	15
PP04	Peter Parker	Lecturer	Micro Economics	7
WW03	Wendy Whitman	Tutor	Economics	1
SS06	Sally Sobers	Lecturer	Securities	5
FF08	Frank Forrest	Tutor	Finance	2

Result table

Course_Number	Lecturer_Code	Tutor_Code	Lecturer_Name	Lecturer_Specialty	Lecturer_Tenure	Tutor_Name	Tutor_Specialty	Tutor_Tenure
1	BB01	GG02	Barry Brown	Macro Economics	10	Gary Gold	Economics	2
2	MM05	Null	Mary Matthews	Accounting	15	Null	Null	Null
3	PP04	WW03	Peter Parker	Micro Economics	7	Wendy Whitman	Economics	1
4	BB01	WW03	Barry Brown	Macro Economics	10	Wendy Whitman	Economics	1
5	SS06	FF08	Sally Sobers	Securities	5	Frank Forrest	Finance	2

I have also tried the solution proposed by @T_Willins in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multiple-columns-to-look-up/td-p/62585...

The trouble I ran into there was when it came to the Cross Tab step and not being able to choose more than one column (go support this idea if you want to be able to choose more than one column: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Multiple-Data-Fields-in-Cross-Tab/idi-p/1731). I can probably have multiple cross tabs in parallel, but once again I was hoping for something a bit more eloquent. 

Thank you in advance.

[Qiu]

@DigitalPlumberDave 
Is this what you need?

If yes, appreciate if you would mark it as acceeptable.

[T_Willins · Accepted solution]

Hi @DigitalPlumberDave,

In this case since the lecturers and tutor codes are in separate fields, you can just use two Join tools to pull the data together.  The Union tool adds back in the rows that don't have a tutor and a Sort tool puts everything in the right order.

[DigitalPlumberDave]

Thank you both @Qiu and @T_Willins for the prompt responses.

In my (inexperienced) view @T_Willins' solutions looks the most elegant so will mark their's as the solution. The incorrect data (Tutor_Specialty and Tutor_Tenure) for course number 5 is my fault for not attaching the sample data - the concept still works a charm.

Two joins was also my initial thought, though this is much nicer than what I envisaged.

---