Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Renaming Records in a Field

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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...;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
JonnyPoole
Employee
Employee

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

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...;


talk is cheap, supply exceeds demand