Requesting help with a multiple key join

[mattlukoff]

Hello, I'm having trouble trying to join two files on two fields and then re-join the results with a new field based on the joined/unjoined.

Instructions

• Join on two keys (ME # and NPI ) .
• Joined have a new field called List = Target
• Unjointed would be List = Specialty

See attached example. Expected ouput wouldn't include results of the inner joint.

Thanks,

Matt

[JoeM · Accepted solution]

@mattlukoff

After looking at your workflow, I decided to "waterfall" joins to continue re-joining unmatched records. I have one record difference between your desired dataset and what I created: record 1 (ME:124 and NPI Num is null). The way I constructed the workflow said that this should not join because the the other record in dataset two matches both on NPI and ME. The change can be made if desired to include that final record. Attached is my solution.

[JoeM]

@mattlukoff

After looking at your workflow, I decided to "waterfall" joins to continue re-joining unmatched records. I have one record difference between your desired dataset and what I created: record 1 (ME:124 and NPI Num is null). The way I constructed the workflow said that this should not join because the the other record in dataset two matches both on NPI and ME. The change can be made if desired to include that final record. Attached is my solution.

[mattlukoff]

@JoeM thank you. Record # 1 (ME 123) shouldn't ever be in the Speciality List because it does match to the Target List. Any of the Numbers/Keys can be considered a match by themselves alone so I removed the BOTH ME and NPI join and I think that solved the issue. Practically I guess I also needed to "Waterfall" the data too.