topic Renaming Records in a Field in QlikView

Renaming Records in a Field

Mon, 03 Nov 2014 13:59:38 GMT

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?

Re: Renaming Records in a Field

JonnyPoole — Mon, 03 Nov 2014 14:03:33 GMT

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

Re: Renaming Records in a Field

Gysbert_Wassenaar — Mon, 03 Nov 2014 14:04:53 GMT

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