topic Re: Find and Replace Multiple Characters in QlikView

Find and Replace Multiple Characters

Sun, 17 May 2015 17:18:43 GMT

I have a field where I want to find and replace a multiple letters within the data of a field. See example below.

11111A

11111B

11111C

I need to replace A, B and C. A should be replaced with 1, B with 2 and C with 3 when present. I know if I just had one character, I could just use the REPLACE function. But in this case I have multiple different characters that could be found within the field. How do I go about search and replacing multiple characters with the data of a field. Hopefully, that makes sense.

Thank you in advanced for your assistance.

Brian

Re: Find and Replace Multiple Characters

Anonymous — Sun, 17 May 2015 17:26:20 GMT

Could you nest the Replace() 's :

=replace ( replace ( replace ( '11111A' ,'A' ,'1' ) , 'B' , 2 ) , 'C' , '3' )

=replace ( replace ( replace ( '11111B' ,'A' ,'1' ) , 'B' , 2 ) , 'C' , '3' )

=replace ( replace ( replace ( '11111C' ,'A' ,'1' ) , 'B' , 2 ) , 'C' , '3' )

Re: Find and Replace Multiple Characters

MarcoWedel — Sun, 17 May 2015 18:21:59 GMT

Maybe you could use mapsubstring.

Regards

Marco

Re: Find and Replace Multiple Characters

maxgro — Sun, 17 May 2015 18:50:10 GMT

Map:

mapping load * inline [

from, to

A,1

B,2

C,3

];

MyTable:

load field, MapSubString('Map', field) as newfield inline [

field

11111A

11111B

11111C

ABC

];