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_NumberLecturer_CodeTutor_Code1BB01GG022MM05Null3PP04WW034BB01WW035SS06FF08 Reference table Educator_CodeEducator_NameEducator_PositionEducator_SpecialtyTenureBB01Barry BrownLecturerMacro Economics10GG02Gary GoldTutorEconomics2MM05Mary MatthewsLecturerAccounting15PP04Peter ParkerLecturerMicro Economics7WW03Wendy WhitmanTutorEconomics1SS06Sally SobersLecturerSecurities5FF08Frank ForrestTutorFinance2 Result table Course_NumberLecturer_CodeTutor_CodeLecturer_NameLecturer_SpecialtyLecturer_TenureTutor_NameTutor_SpecialtyTutor_Tenure1BB01GG02Barry BrownMacro Economics10Gary GoldEconomics22MM05NullMary MatthewsAccounting15NullNullNull3PP04WW03Peter ParkerMicro Economics7Wendy WhitmanEconomics14BB01WW03Barry BrownMacro Economics10Wendy WhitmanEconomics15SS06FF08Sally SobersSecurities5Frank ForrestFinance2 T_Willins in this post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multiple-columns-to-look-up/td-p/625853 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.

Joining multiple columns with one reference column

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

T_Willins in this post:

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

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.

Workflow

Developer

Transformation

Join

Accepted answers

T_Willins

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.

Join multiple columns.jpg

Join multiple columns.yxmd

All comments

Qiu

@DigitalPlumberDave
Is this what you need?

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

1029-DigitalPlumberDave.yxmd

T_Willins

Hi @DigitalPlumberDave,

Join multiple columns.jpg

Join multiple columns.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703