1 Reply Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    External lookups missing unmatched data

    ChrisA _

      I have a monarch file which I do an external lookup to an mdb. file. Everything works fine except when there is no match from the mdb file the entry is not brought in.



      Monarch xmod-life producers

      external lookup file (.mdb)- disability producers


      I link them by the producer number (ex:12340000). Each producer has their own number. Most of the life producer numbers and disability producers match but I have a disability producer number that has no life match. I sthere a way of still bringing that record over?

        • External lookups missing unmatched data
          Grant Perkins

          Hi Chris,


          Monarch uses a left outer join for lookups so the match will bring in anything that can be matched to the prime fiel records but will ignore anything where there is no match. Typically this is what is required for a lookup.


          In your case you want to bring in a record (or more than one potentially?) which has no related lookup link. If the rest are one to one records would working the other way around be an option? IE. use the mdb database as the prime file and the report to provide the lookup?


          I suspect that might not be what you want however, so here are a couple of extra options.


          Idea 1.


          Add the missing code to the prime list by adding a record with the number. You could simply type it into the file at the end of the report or perhaps have another 'report file', containing only the missing number(s) in the correct position to be picked up by the model template,  which you open at the same time as your main report file.


          Idea 2.


          Run a pre-process that create a 'prime' file of the link numbers from both sources and then use that number list linking both the report file and the database to it. (I am assuming you have version 7 or 8 Monarch allowing multiple external lookups.)


          I have used this idea to link 3 files for which common keys could be derived but where each relationship had the possibility for multiple record links. The only logical way to ensure all possible combinations were created was to define a master list first and then link all three files to it to extract the records. It was far easier than trying to 'see' and interpret the links one at a time. For some of the records I had to generate a master key that did not otherwise exist for the purpose of my analysis. It was logical to do this in advance rather than try to do it on the fly.


          I hope this provides you with some ideas to work with.