Renaming Records in a Field

[#1]

Struggling to understand how to handle ugly or messy records in a field.  For example,  In my products field, there are text AND number records --> the numbers do correspond to specific products ( I want to change them to the text names).  should I create some kind of mapping table?  How should I do this?

[JonnyPoole]

Sounds like the product field has a mix of both numerical 'product IDs' as well as some other textual attribute of the product (name, description? etc.. )

You could do an conditional applymap() function to replace the numerical values with textual values coming from a mapping table.

A simple check if its > 0 will check if its numerical or not:

LOAD,

     IF(  Product > 0 ,  applymap() ,  Product)   as CleanedProductName,

     ....

From <source>;

[Gysbert_Wassenaar · Accepted solution]

You could create a mapping table and use the applymap function to replace product codes with product names:

ProductMap:
mapping LOAD ProductID, ProductName

FROM ...table_with_productcodes_and_names... ;

MyData:

LOAD

     ...some_fields...,   

     applymap('ProductMap', MessyField) as Product,

     ...some_more_fields...

FROM ...messy_source_table...;