3 Replies Latest reply: May 15, 2014 10:02 AM by ChristyJ _ RSS

    External Lookup

    ChristyJ _

      My goal was to link 2 reports together by using External lookup, I have successful done that. But I have a couple of questions.


      1) Both reports has most of the same names. But the names are slightly different. Example: one report has John N Smith, the other one is John Smith. Both names are the same person just type in differently. Is there a way for me to filter them so they will match?


      2) Is there a way for me to include the names from the data source that don't match up together into the report?



        • External Lookup
          Grant Perkins

          Hi ChristyJ and welcoome to the forum.


          I'm not sure how to best answer your question but here goes.


          I have worked on problems like yours before. You can do a lot with Monarch to create links my manipulating information that starts out looking different in some way. No problem.




          you need to be sure of the data and way they match across the systems and whether they are indeed uniquely linkable. Quite frankly names are not a great way to identify people - it's amazing how often they are not unique even in small databases. Sometime they work out, sometimes the best you can do if flag up a possible problem and maybe request a change in the original data (or output file) to get things improved.


          Identification references - employee number, customer number, SSN, etc., are much better, though still not perfect.


          You can only link on the minmum level of information in either file so in this cae that would be "johnsmith" or, maybe, "smithjohn" for example.


          We can explain how to make "John Smith" or "John N Smith" become johnsmith but you would just have to hope you never get another John Smith in either list!


          As for question 2 I take it to mean that each list may have names that do not occur on the other list. In which case probably the best approach and it is helpful for the first problem as well) is to make a master list of all the names in each input report in a single field (probably) single file and the use that to link to information in BOTH of the other reports after you have reduced the list by filtering for duplicates. (If you get a COUNT in a summary that shows more than 2 for a single name you know you have a problem!)


          How does your data look after considering these points?





            • External Lookup
              Bruce _



              Grant is bang on you will have to go through each database & assign an unique index to each one. For the purposes of this example we cannot tell if John N Smith is the idential person to John Smith, (maybe the 2nd guy's middle name is Quinten!) so there is no way to program Monarch.


              However that being said Monarch does have a Nname Split function (NSplit) that will break a name down into Prefix, first name, last name, middle name or initial, suffix. Once you have 2 sets of data with the names parsed out you can decide if you have duplicate data. So perhaps for your data if you have the exact same first name and last name they are the same person. However on a large database you might have:

              John Smith, John N Smith, John Smith Jr., from this context we cannot tell if they are in fact the same person.


              Lots of luck!