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

Question

I'm working in version 2021.4.

I have two sets of data that look like this:

Employee CodeEmployee Supervisor CodeEmployee MGR CodeEmployee Group VP CodeEmployee Supervisor Pay GradeEmployee MGR Pay GradeEmployee Group VP Pay GradeAAAAAA111AAAAAA222AAA333---BBBBBB111BBBAAA223AAA334---CCCCCC111CCCAAA224AAA335---DDDDDD111DDDAAA225AAA336---EEEEEE111EEEAAA226AAA337---FFFFFF111FFFAAA227AAA338---

Employee CodeEmployee Pay GradeAAAAAAA1BBBBBBA2CCCCCCA3DDDDDDA4EEEEEEA5FFFFFFA6111AAAB1111BBBB2111CCCB3111DDDB4111EEEB5111FFFB6AAA222C1AAA223C2AAA224C3AAA225C4AAA226C5AAA227C6AAA333D1AAA334D2AAA335D3AAA336D4AAA337D5AAA338D6

I need to join them so the resulting data looks like this:

Employee CodeEmployee Supervisor CodeEmployee MGR CodeEmployee Group VP CodeEmployee Supervisor Pay GradeEmployee MGR Pay GradeEmployee Group VP Pay GradeAAAAAA111AAAAAA222AAA333B1C1D1BBBBBB111BBBAAA223AAA334B2C2D2CCCCCC111CCCAAA224AAA335B3C3D3DDDDDD111DDDAAA225AAA336B4C4D4EEEEEE111EEEAAA226AAA337B5C5D5FFFFFF111FFFAAA227AAA338B6C6D6

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.

albert_alaluf · Answer

Hello @Rob48

Do you think this works for you?

join cols.yxmd

summit_view · Answer

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.

danilang · Answer

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

JoinSingleColumnToMultiples.yxmd