0

Finding UnMatched in LookUp

Brad Elmore

July 30, 2014 14:38

None

Follow

I have a data flow map that has an excel sheet as the source.....the sheet contains 200 records....My 1st goal is to find related records within my MySQL database.....so I created a SQL Statement Lookup Transformation.....WORKS GREAT!! I found 100% matches (200 excel records matching 200 mysql database records)....

However....within MySQL database I have 250 records......I know that out of the 250 records...I have 200 that match the 200 in the excel sheet.....What I Do Not Know is which 50 records are missing from the Excel sheet....how can I find which 50 are missing??? Is there a "Find UnMatched" transformation???

Thank You

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Brad Elmore July 30, 2014 14:46

  Got it!! Thanks for an Awesome Product!! 

• 0
  
  Mike O'Quinn July 30, 2014 17:34

  If I understand you correctly, there are two ways to do this.  Either with a lookup or a join transformation.  

  Lookup: Lookup by releated key, but set the properties for unmatched option to "null".  This will result in 200 records having a value retrieved from the lookup and 50 records with null in that column.  Then, place a filter object after and set the expression to IsNull() and pass in the column that has null and non-null values.  This should weed out the records that have matches and leave the ones with no match.  

   

  Join: Same as lookup, but you are doing it in one go.  Set the join to "left outer".  This will result in a some records coming from your MySQL database that have null values where the Excel data would be.  Again, filter the non-null records out to leave just the unmatched.

• 0
  
  Brad Elmore July 30, 2014 17:36

  Mike, Got it working!! Thank you.