Create and join columns based on multi row values

mlim0806

Hi Community, 

I have a dataset that has duplicate ID's that will be used as the "parent" file. I want to take data from the supplemental file and create two new columns, "on at least one long term plan" and "on at least one short term plan". The values will either be yes, no, or blank. Notice the supplemental file also has duplicate IDs and the plan length value may differ. 

For example, ID# 111 only appears twice on the parent file, but the output will add two more rows for 111 since they have both a long term and short term plan. I only want to keep the original rows from the parent file, but the join and union does not seem to be doing what I expect. Please let me know if you can point out where the issue is. TY. 

Sample_Create Columns on Multi Row Data.yxmd

CoG

I modified your workflow to create your desired output. I made 2 modifications: Adding a summarize tool to Group By [ID] column, using "Longest" since "yes" is longer than "no" this will allow you to capture data properly, then I added a formula after your Join tool so that in the event that an [ID] from the parent table is not in the Supplemental File, you are not left with nulls after the union.

Sample_Create Columns on Multi Row Data.yxmd