Join single column of data from one set to multiple columns in another

[Rob48]

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.

[danilang]

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

[summit_view]

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.

[albert_alaluf]

Hello @Rob48 

Do you think this works for you?