Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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]

View solution in original post

6 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

johnw
Champion III
Champion III

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]

Not applicable
Author

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