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.
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.
Load applymap('map_new_product', Product) as [New Product Name],
Load * from product_table.xls
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.