cleaning up values in a field

[Mark4]

Hello,

I have a mapping table combining two fields:

Customer_Name:
Mapping Load
"Field 1",
Trim([Field 2])
Resident [Imported Data];

ApplyMap('Customer_Name', "Field ID", "Customer Name") as Customer,

Producing the following field:

Is there a way to clean up this combined data without doing WildMatch() or Like() for each individual value?

I'm looking for an all inclusive Like() function, if one exists....

Any ideas are welcome, thank you.

[Lisa_P]

A mapping table is the best way to deal with this situation.  Old Name and New Name fields to convert all of these to the name you want.

[Rodj]

Not really is the direct answer to your question. It kind of depends on how you want to do it. This appears to be a data quality issue essentially and as such I'd have a separate process to identify the records I don't want and then use a mapping table to clean them up, which basically looks like what you are doing, you are just missing some values from your mapping table (if I'm understanding correctly what you're asking?).

As it's a data quality question I think it is important to be able to expose, track and document what you are cleaning up, so exposing a list of cleaned up records in a "data quality" sheet or similar can be very handy, just in case you clean something up that you weren't meant to. Better yet I like to challenge people to expose the poor quality data to the end users as they are often in the best position to get it cleaned up. It might just mean they need to select more than one value to get the answer they want. I totally understand though that is a philosophical argument that you might not feel comfortable getting into with your customer.

Cheers,

Rod