Stacking Two Set of Data Side-by-Side

[knnwndlm]

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

[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

[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.