Stacking Two Set of Data Side-by-Side

Hi SME,

I have two sets of data shown in the RAW DATA tab in the attached file. The RESULT tab is what I want to see when I stack the two sets of data next to each other. I couldn't get the line up by matching classes and years using the Join by Record Position in the Join tool. If I tried to do this using the Join by Specific Fields with Class and Year, I would get duplicate values for Class B.

Any suggestions on how I can stack the two datasets to get the RESULT tab?

Thanks,

kwl

Side By Side Comparison.xlsx

Preparation

Accepted answers

jrlindem

@knnwndlm

I've added the Row Number, modified the Join, and added a couple of Select tools to remove the Row Number fields from persisting downstream. This ensures that only one copy of each sides record gets joined (avoids duplication noted before). Here's the revised workflow and result (and attached).

Does this match what your need was? -Jay

Example_Solution_jrlindem_v2.yxzp

All comments

jrlindem

I'm sure there are many ways to get what you are asking for. Here's one such option that solves your ask and I think is illustrative enough for you to adapt (below and attached).

Edit: Just noticed I put the years in the wrong order. Switch that in the Sort Tool to Descending instead of what I attached and show (which is in ascending).

Hope that helps, -Jay

Example_Solution_jrlindem.yxzp

knnwndlm

Thanks @jrlindem - I see that Amt (A) has dups - rows 13-14 in Amt (A). I've been struggling to fix this prior to the post.

jrlindem

@knnwndlm

It's because your "B" set has two values that match on Class and Year; so when you join it's not able to "pick for you" which A values to relate to so it shows the "A" values on both lines of unique "B" values.

You could probably create a Row Number for each combo of Class and Year for both the "A" set and "B" set and join across Class, Year, and RowNumb to stop that from happening.

Edit: Depending on your Alteryx version the Record ID tool will have a group by option (or not). If yours doesn't (like mine) you may need to use the Multi-Row Formula tool instead.

jrlindem

Does this match what your need was? -Jay

Example_Solution_jrlindem_v2.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703