5 Replies Latest reply: May 15, 2014 10:00 AM by Bruce _ RSS

    Lookup tables

    BethG _

      Hi -

       

      I am wondering if there is a way to do look-up tables in Monarch based on columns that contain parts of the customer name, but not necessarily the entire name?

       

      When doing external lookup tables to gather contras (customer names reporting on 2 different reports), we are having a hard time pulling up all the duplicates because a lot of times customers will have 'INC' on the end of their customer name in one file, but not the 2nd file (for example, 'ABC Company INC.' and 'ABC Company').

       

      I thought about re-calculating the customer names on the 2 files so that we only grab the first word of the customer name, but in some cases, that will not work (for instance, 'ABC Company' and 'ABC Brothers' would suddenly be compared by Monarch as one company).

       

      An internal lookup table is one option, however, we can have up to 500 customer names, and over 600 reports...and that is a lot of manual upkeep.

       

      Thanks!

        • Lookup tables
          Data Kruncher

          Hi Beth, and welcome to the forum.

           

          Do the customer names differ between the two reports simply because one report gives you, say, 11 characters of the name, and the other report gives you, say, 20 characters?

           

          Or is there something else going on, like all of the Inc., Ltd., etc. get dropped? This would be pretty unusual, but stranger things seem to get programmed from time to time.

           

          Kruncher

            • Lookup tables
              Olly Bond

              Hi Beth,

               

              It's a common problem, and I'm afraid there's no magic bullet. Like you say, stemming the key field to the first word is often too crude a filter. But you can try a "fuzzy" approach - create a calculated field which stems to the first word, and one to the first two and so on, then create external lookups joining on these. Then filter the null values appropriately so that you get the most exact matches scoring highest - "ABC Company Inc of Michigan" might score 5, down to "ABC" matching scoring 1. You can always use the UPPER() and STRIP() functions to help get matches - you don't want Abc not to match A.B.C.

               

              Watch out for running in to too many fields, exceeding the maximum data length, having too many external lookups in a project etc.

               

              HTH,

               

              Olly

                • Lookup tables
                  BethG _

                  Thank you both for your help and advice -

                  Unfortunately, the names are indeed programmed differently in the 2 different reports. And with so many of them, I'm not sure a manual lookup table was going to help me much since the customer names could change month to month.

                   

                  I may try the 'fuzzy' approach for a few of the known offenders and see how that goes.

                   

                  Thank you both!

                    • Lookup tables
                      Grant Perkins

                      Beth,

                       

                      If you know you should be able to expect a match month my month you might consider first analysing just the two sets of names with a view to pre-preparing them for a later connection.

                       

                      So, thinking 'aloud', to a match comparison for just the names (or names and anything else that might help!) between the two reports for a period. You might also 'normalise' the names, taking out spaces and punctuation for example, and check the results when the normalised fields are compared as well. One could do more perhaps but let's keep it simple for now.

                       

                      If both checks are OK you would appear not to have a problem - though there might be exceptions to that but is there are then you have some real data matching issues anyway that would require much more rigorous attention!

                       

                      If one matches but the other doesn't a small correction may be required. If a name has no matches it would need to be investigated. It is likely that relatively few unmatched records will appear if the data is at all reasonable.

                       

                      From the resulting extractions you could build a simple look up file for use as an EXTERNAL lookup. Make this a notepad text file, or perhaps a simple Excel table and it would be easy to amend it as required  - manually if necessary and therefore derive an accurate and usable cross reference table as a basis for future use. Presumably once created the lookup entry would have some consistency and use period by period so changes are likely to be low volume.

                       

                      Just a few thoughts to mull over.

                       

                       

                      Grant

                        • Lookup tables
                          Bruce _

                          Beth

                           

                          Just a thought, you mentioned that the names COULD change month to month. Does this mean many remain the same over time?

                           

                          If that is the case I would make an outside table with a key field, and both variations of the customer names.

                           

                          i.e. Customer Key 1, ABC Inc, ABC incorporated.

                           

                          Then use this table every month and update as required. Then use a Monarch look up to see if you can find either spelling.

                           

                          OR do the customers change so much that this would not be possible at all??