add field identifying which join criteria failed

Question

Hello,

How would I be able to add a field that tells me what caused the join to fail? I'm doing a join on 4 fields (CompanyID, DeptID, JobID, GroupID), and from the left results, I want to be able to identify which of the 4 fields (or if it was all 4 fields) did not match. For example record 1 did not match on CompanyID and DeptID, record 2 did not match on GroupID, record 3 did not match on any field, etc. My goal is to do a union with the left and middle results to have the full population again but to be able to identify which records all matched and which ones did not match and why they didn't match. Thanks.

DawnDuong · Answer

Hi @elclark

One commonly used method is to put a UNION after the JOIN to union all the L, J and R outputs to get the “Combination” (imagine a Venn Diagram that shows you the combined area covered by at least one of the 2 circles).

in this way, those fields which did not match will have [null] values

dawn

Qiu · Answer

@elclark

I hope this is what you need.

Sample-r1.yxmd

elclark · Answer

Here is a sample workflow with data. Essentially I want a way to identify (even if it's not with the join function) that says for the Left results, these are the reasons it didn't match. Here are two examples of what I would like the final output to look like, doesn't have to match these exact formats but something along those lines.

NameCompanyIDDeptIDJobIDGroupIDGroupNameFields Not MatchedJANE SMITH100100050008200ManagerGroupIDJILL DOE200110070008200ManagerJobID, GroupIDJACK DOE300100070008300DirectorCompanyID, JobID, GroupIDJILL SMITH300200070008300DirectorCompanyID, DeptID, JobID, GroupIDJANE DOE100100050008000AnalystFull MatchJOHN DOE100100050008100SeniorFull MatchJOHN SMITH200110060008000AnalystFull Match

OR

NameCompanyIDDeptIDJobIDGroupIDGroupNameDoes CompanyID MatchDoes DeptID MatchDoes JobIDMatchDoes GroupID MatchJANE SMITH100100050008200ManagerYESYESYESNOJILL DOE200110070008200ManagerYESYESNONOJACK DOE300100070008300DirectorNOYESNONOJILL SMITH300200070008300DirectorNONONONOJANE DOE100100050008000AnalystYESYESYESYESJOHN DOE100100050008100SeniorYESYESYESYESJOHN SMITH200110060008000AnalystYESYESYESYES

Sample.yxmd