Compare Data in 2 Sources with a primary key

[vdataqlalt]

My requirement is to create a report after comparing data between two source ie compare employee data between two sources and identify the employee data is in sync between two sources.

SOURCE A

EMP_NUM   NAME    CITY

1                          JO             Dubai

2                          KO             Paris

SOURCE B

Source B

EMP_NUM   NAME    CITY

1                      JO            Newyork

2                      KO           Paris

My report should compare each employee raw by raw and create a report like below

A.EMP_NO	B.EMP_NO	EMP_NO_MATCH	A.NAME	B.NAME	NAME_MATCH	A.CITY	B.CITY	CITY_MATCH
1	1	Match	Jo	Jo	Match	Dubai	Newyork	Mismatched
2	2	Match	KO	KO	Match	Paris	Paris	Match

how can we do this? 

[bbak91 · Accepted solution]

I normally like to do a transpose/crosstab approach as seen below because it makes it easier to read across your data and filter if desired. Workflow is attached for reference. 

 

[vdataqlalt]

thanks ..there are chances that the record in source A  is completely missing in source B..how can we report that 

[bbak91]

You can add a union tool directly after the join and have both the L, the J, and the R from the Join tool feeding into the union. This way all records will come through even when there isn't a match.

[vdataqlalt]

Thanks.I have added a Union, but after the transpose, I get the records which are common only,

if I get a separate column telling 'Present In Both' , 'Not In Source B', Not In Source A' that is also fine