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. 

[CoG · Accepted solution]

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.

[CoG]

I also wanted to say, your included data (Sample Input and Output in Sample Workflow) were very well done. Made things quite clear and was very helpful. So thank you for that!!!

[cjaneczko]

Try this.

[mlim0806]

Thank you, and thank you for your help! The added summarize tool worked, and I didn't even think to add a formula for the nulls that I may get on the parent file. This worked great!