Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]
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.
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.
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
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.
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]
John and Leonard... you guys are amazing. Thanks again.