How do I get non-matching records from two tables in Magic ETL

lawiti

I have two tables A and B , a unique record is identified by an ID and a date. I am interested in identifying the non-matching records in the two tables and using the output to build a report,how would I go about this?

MarkSnodgrass

@lawiti you could add the date fields to your join criteria and then you could look for the nulls. You could also just join on the ID column and then use a filter tile after that and filter to where TableA date does not equal TableB date.

GrantSmith

IN a magic ETL Use a full outer join on the id columns and then you can filter the join where table a’s ID is null OR table b’s ID is null

lawiti

@GrantSmith , thanks for your suggestion. Let me give more context, Tables A and B are very similar, they both have exact same attributes and records the only thing changing from time to time is the date on on the records, essentially I would like to single out the records whenever the date changes/gets updated. So a full outer join may not work here since at no point will the ID nor the date be null.

MarkSnodgrass

@lawiti you could add the date fields to your join criteria and then you could look for the nulls. You could also just join on the ID column and then use a filter tile after that and filter to where TableA date does not equal TableB date.

lawiti

Thanks, @GrantSmith and @MarkSnodgrass , it's working.