Find and replace data with specific patterns

[#1]

Hi there!

I was wondering if there is a better way to replace certain text patterns with another.

The reason: there are certain identifications which are used randomly in a clients dataset, but can no longer be used to ensure anonimity.

I know there is a scramble function, but we only want to tackle those identifiers which might reveal unwanted information.

Attached a simplified inline example.

[CODE]

ReplaceMyData:

LOAD

     Name as [Original name],

     if(substringcount(Name,'Van')=1,

         replace(Name,'Van','_REPLACED_'),

              if(substringcount(Name,'VAN')=1,

                 replace(Name,'VAN','_REPLACED_'),

                      if(substringcount(Name,'VAN')=1,

                         replace(Name,'van','_REPLACED_'),

                              if(substringcount(Name,'Mario')=1,

                                 replace(Name,'Mario','_REPLACED_'),

                                     if(substringcount(Name,'WILDER')=1,replace(Name,'WILDER','_REPLACED_'), Name))))) as [Converted name]

FROM

[SourceTable.qvd]

(qvd);

[/CODE]

Thanks in advance for your help!

Regards,

Maarten

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

You could use the MapSubString with a mapping table like in the attached application.

Hope that helps.

Miguel

[#3]

Miguel,

That is exactly what I was hoping for! 🙂

Thanks a ton!

Kind regards,

Maarten