5 Replies Latest reply: May 15, 2014 10:14 AM by MonUserCJ _ RSS

    Large external lookup not working

    MonUserCJ _

      Hey all,

       

      I have a large external lookup (the mapping file contains about 80,000 rows) that is not working.

       

      Basically, the situation is that I am converting records for people. Each person has 2 IDs. I have one file that uses the first ID, and then another file that contains both IDs.

       

      Thus, I should be able to map ID B to ID A using the mapping file that contains both. I do this, and it gives me no errors, but all the imported columns come out null. I also tried using a small subset of that file to see if the number of records was a problem. It still doesn't work.

       

      Does anyone know of any constraints on the size of the lookup mapping files or the amount of data that can be looked up? Otherwise, does anyone know of any other way to map the second set of IDs? I can do it looking up the patient IDs in the database, but then I'll have to have 2 sets of IDs in the data.

       

      I would appreciate any advice that anyone can provide.

        • Large external lookup not working
          Olly Bond

          Hello CJ,

           

          There's nothing problematic about opening a lookup to 80,000 rows, as long as it's in a data format that can handle that (unlike .xls).

           

          What version of Monarch are you using, what happens when you click on Refresh Database and External Lookups, and is the lookup on a network location or local?

           

          Best wishes,

           

          Olly

            • Large external lookup not working
              MonUserCJ _

              It's a delimited text file. I'm using Monarch Pro 10.5. Refresh Database and External Lookups doesn't fix it.

               

              It is linking on a character field that contains numbers. One thing I will say is that I put a row of "A"'s at the top of the file. This is because, without it, Monarch would try to match the column as a number even though the numbers were in quotes. Any advice would be appreciated.

                • Large external lookup not working
                  Grant Perkins

                  Are the linked fields actually coming up as NULLs or blanks?

                   

                  If nulls it would  suggest that the receiving field is defined as a numeric and that it is receiving Character data - hence the NULL.

                   

                  If it's  character field receiving characters and yet it's blank blank then there is something else going on but I would suggest going back to basics and re-checking you definitions as a first step. Many is the time that the simple thing that could not possibly be the problem, er, was in fact the problem. Sometimes modelling is like proof-reading -  very difficult to spot the glitches in something you have created.

                   

                  If neither of the above ... then we will to think up a new question or two.

                   

                  Grant

                    • Large external lookup not working
                      Olly Bond

                      Hi CJ

                       

                      My first thought would be to convert the delimited text file to an Access .mdb format with a separate model & project.

                       

                      Then make the external lookup as before and you can be confident that Monarch won't have a problem with the field type.

                       

                      Best wishes,

                       

                      Olly

                        • Large external lookup not working
                          MonUserCJ _

                          Hmm, actually, it seems to be working fine now. Sorry about that. I'm not sure why it wasn't working before.

                           

                          Also, thanks for the tip Olly! If I export to Access, I can use the external lookup without adding the row of A's. This lookup will help me keep my code clean.