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

lawiti
lawiti Member
in Magic ETL

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?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • lawiti
    lawiti Member

    @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
    MarkSnodgrass Coach
    Answer ✓

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • lawiti
    lawiti Member

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

Categories