Join single column of data from one set to multiple columns in another
- 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
I'm working in version 2021.4.
I have two sets of data that look like this:
Employee Code | Employee Supervisor Code | Employee MGR Code | Employee Group VP Code | Employee Supervisor Pay Grade | Employee MGR Pay Grade | Employee Group VP Pay Grade |
AAAAAA | 111AAA | AAA222 | AAA333 | - | - | - |
BBBBBB | 111BBB | AAA223 | AAA334 | - | - | - |
CCCCCC | 111CCC | AAA224 | AAA335 | - | - | - |
DDDDDD | 111DDD | AAA225 | AAA336 | - | - | - |
EEEEEE | 111EEE | AAA226 | AAA337 | - | - | - |
FFFFFF | 111FFF | AAA227 | AAA338 | - | - | - |
Employee Code | Employee Pay Grade |
AAAAAA | A1 |
BBBBBB | A2 |
CCCCCC | A3 |
DDDDDD | A4 |
EEEEEE | A5 |
FFFFFF | A6 |
111AAA | B1 |
111BBB | B2 |
111CCC | B3 |
111DDD | B4 |
111EEE | B5 |
111FFF | B6 |
AAA222 | C1 |
AAA223 | C2 |
AAA224 | C3 |
AAA225 | C4 |
AAA226 | C5 |
AAA227 | C6 |
AAA333 | D1 |
AAA334 | D2 |
AAA335 | D3 |
AAA336 | D4 |
AAA337 | D5 |
AAA338 | D6 |
I need to join them so the resulting data looks like this:
Employee Code | Employee Supervisor Code | Employee MGR Code | Employee Group VP Code | Employee Supervisor Pay Grade | Employee MGR Pay Grade | Employee Group VP Pay Grade |
AAAAAA | 111AAA | AAA222 | AAA333 | B1 | C1 | D1 |
BBBBBB | 111BBB | AAA223 | AAA334 | B2 | C2 | D2 |
CCCCCC | 111CCC | AAA224 | AAA335 | B3 | C3 | D3 |
DDDDDD | 111DDD | AAA225 | AAA336 | B4 | C4 | D4 |
EEEEEE | 111EEE | AAA226 | AAA337 | B5 | C5 | D5 |
FFFFFF | 111FFF | AAA227 | AAA338 | B6 | C6 | D6 |
right now I'm playing with copying all the Supervisor/Manager/VP codes directly into the pay grade columns, then use find and replace once each per column to get the pay grades in, but this seems messy and too many repeating steps, and the issue is my actual data that the first table represents has many more than 3 columns, more like 10-15.
My second table is only two columns like the example.
Is there a quicker way to integrate the two data sets than manually adding in multiple Find/Replace or Join tools so I can get to the last table format? Thanks for any help you can provide.
- Labels:
- Dynamic Processing
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Rob48
Any time you have a situation where you need to perform the same operation on multiple columns, always consider whether it might be more efficient to transpose the columns to name/value pairs. This allows you to perform a single operation on the new pair of columns and also guards against the case where the columns can change in future scenarios.
Here I transpose the columns in question and then perform a single Join on the Pay Grade table to get all the related pay grades. Cross tabbing this new data gives us a new set of three columns that can be joined back to the original data. The other tools in the workflow are to build the new pay grade column names and to deal with the issue that the Cross Tab tool can position the new columns alphabetically, as opposed to logically. You get around that by using an integer field, [ColumnOrder] as the new column names and then use a Dynamic Rename to set the correct field names.
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
HI @Rob48
I Transposed the Data and then used Cross Tab to get it back into records by record ID (employee). I split the data to do the cross tab of code and pay grade separately.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @Rob48
Do you think this works for you?
Alteryx ACE
https://www.linkedin.com/in/alaluf/
