Joining multiple columns with one reference column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
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.
Solved! Go to Solution.
- Labels:
- Developer
- Join
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@DigitalPlumberDave
Is this what you need?
If yes, appreciate if you would mark it as acceeptable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
