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

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

Thu, 07 Jul 2011 18:24:14 GMT

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!

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

Thu, 07 Jul 2011 18:47:58 GMT

Is it just as simple as eliminating a few unwanted characters? You could do replace(Product,'_',' ') as Product and it would eliminate all of the underscores in that field during your load script.

If it is more complex than that then do a search in the help file for applymap as that could also be useful in this type of situation.

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

Thu, 07 Jul 2011 18:53:54 GMT

Leonard-

Thanks for the tips. However, the problem is that the Product field is constantly evolving and changing. This involves constant user interaction to "clean up the data." The examples that I presented were greatly simplified. A more real-world example would be something like:

Product, New Value

Anthelios XL : Sun Block : Cream, Axe

Aussie : Hair Care & Styling Products, Axe

Aveeno Ageless Vitality : Anti-Aging Facial Moisturizer System : Cream & Serum, Aveeno

Aveeno Baby Continuous Protection : Sun Block : Lotion, Johnson and Johnson

ApplyMap() would work if it only involved a few changes here and there, but the current changes would remain constnant but there will definitley be future updates for new values that need to be "cleaned up" and matched up.

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

Thu, 07 Jul 2011 19:20:15 GMT

If you have a table/spreadsheet that contains all of the currently possible Product's & their new names, then Applymap is perfect for you. Just update the table/spreadsheet with any new values and the next reload will update the app.

map_new_product:

Mapping Load

Product,

New Product

From NewProductList.xls;

Product_Table:

Load applymap('map_new_product', Product) as [New Product Name],

Load * from product_table.xls

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

Thu, 07 Jul 2011 19:49:55 GMT

Leonard-

Definitely helps. I think the other issue is that there are constantly new Products showing up that may (or may not) need to be "cleaned up."

For most scenarios, I don't think this is a huge problem. We could (for example) create a "systems tab" where all values appear which would show the "blank" new values.

However, there are certain tables where after the matchup occurs, any areas of nulls, all the rows containing null values under Product would get removed during the load process.

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

johnw — Thu, 07 Jul 2011 19:58:42 GMT

The third parameter of applymap() is a default value. To default to the product you were given, just put it in as the third parameter:

applymap('map_new_product',Product,Product) as [New Product Name]

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

Thu, 07 Jul 2011 20:22:53 GMT

John and Leonard... you guys are amazing. Thanks again.