6 Replies Latest reply: Jul 7, 2011 4:22 PM by w00master RSS

    Matchtable/Lookup Table Scenarios (& Advice on Best Practices?)

      Apologies that I keep pinging the QV Community, there's still a lot that I'm trying to learn and definitely appreciate all the help that I've received so far. That being said. I have a situation where many of our tables (specifically the values contained in the tables) do not contain "user friendly readable" information.

       

      So, in the past, our various teams created "lookup tables" or "match tables" to replace the non-user friendly readable names with more user-friendly names. Example:

       

      Table 1:

      Name, Division, Product, Cost

      John, East, Franklin 2, 3.00

      Mike, West, Franklin_2, 3.00

      Mary, West, Franklin, 3.00

      Mary T, Central, Joseph, 10.00

      John, East, Joseph_1, 10.00

       

      Table 2 (Matchtable)

      Product, New Product

      Franklin_2, Franklin 2

      Joseph_1, Joseph

       

      Note that we obviously do not have any sort of "key."  All is matched on Product (a text value).

       

      So in excel, what these folks would do is for any match found in Table 2, replace Product with "New Product."  If not, then keep "Product."

       

      Thoughts on how to implement this?  Also, what is everyone's views/advice on best practices on this type of scenario?  Please note, that we have no way of user "forcing" data entry integrity - these items (specifically Product) is filled in from a 3rd party that we have no control over.

       

      Thanks!