topic Re: How can I delete specific characters from a string variable? in QlikView

How can I delete specific characters from a string variable?

mlarruda — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I have a variable/column called NAME, where some names start with "Dr ", Mr" etc. and I want to delete this to make "Mr. James" become only "James", for example.

I did this subroutine:

SUB CleanName (SourceString)

IF Left(SourceString,3) = 'Mr '

SourceString = Right(SourceString,Len(SourceString)-3);

IF Left(SourceString,3) = 'Ms '

SourceString = Right(SourceString,Len(SourceString)-3);

IF Left(SourceString,4) = 'Mrs '

SourceString = Right(SourceString,Len(SourceString)-4);

IF Left(SourceString,3) = 'Dr '

SourceString = Right(SourceString,Len(SourceString)-3);

END SUB

And called it by typing

CALL CleanName(NAME);

in the last row of the Main tab, nut it didn't work.

Any friend can point me where is the error or how can I do what I want?

Re: How can I delete specific characters from a string variable?

stascher — Wed, 14 Feb 2018 17:47:40 GMT

In your load statement for the column NAME, try the following:

Load

if(match(subfield(NAME,' ',1),'Mr','Ms','Mrs','Dr'),subfield(NAME,' ',2),NAME) as NAME

from.....

Re: How can I delete specific characters from a string variable?

lcontezini — Wed, 14 Feb 2018 17:52:00 GMT

if(left(SourceString,2)='Ms', replace(SourceString,'Ms ',''),

if(left(SourceString,2)='Mr', replace(SourceString,'Mr ',''),

if(left(SourceString,2)='Dr', replace(SourceString,'Dr ',''),

if(left(SourceString,3)='Mrs', replace(SourceString,'Mrs ','')))))

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 17:55:55 GMT

Thanks a lot, but how this code will work: Will a name like "Feodr Petrov" be changed to "Feo Petrov" or simply "Petrov"? I DON'T WANT that such changes happens, because here the "dr" is parte of the name rather than an abbreviation of "Doctor".

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 17:56:44 GMT

Thanks a lot, but this code will not avoid that names like "Feodr Petrov" be changed to "Feo Petrov". I DON'T WANT that such changes happens, because here the "dr" is parte of the name rather than an abbreviation of "Doctor".

Re: How can I delete specific characters from a string variable?

stascher — Wed, 14 Feb 2018 18:06:53 GMT

It will not alter Feodr Petrov because subfield(1) returns Feodr which does not match any of the strings 'Mr','Mrs','Ms' or 'Dr'

Re: How can I delete specific characters from a string variable?

lcontezini — Wed, 14 Feb 2018 18:20:12 GMT

This code will replace only if the initials of the name are 'Dr', 'Ms'...

In the case of "Feodr Petrov", the code left(SourceString,2) will return FALSE and there will be no replacing.

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 18:58:47 GMT

But if the string is "Dr Feodr Petrov" your code will replace both "dr", remaining "Feo Petrov"...

Re: How can I delete specific characters from a string variable?

zebhashmi — Wed, 14 Feb 2018 19:15:33 GMT

your code looks good

can you use it in load

IF(Left(SourceString,3) = 'Mr ',Right(SourceString,Len(SourceString)-3), next one, as SourceString;

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 19:34:47 GMT

Thanks, but didn't work. The subfield(NAME,' ',2) is extracting only the 2nd word of the string. E.g. "Mr James Smith" is returning only "James" instead of "James Smith".

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 19:54:06 GMT

Thanks but didn't work.

Re: How can I delete specific characters from a string variable?

mlarruda — Wed, 14 Feb 2018 19:57:07 GMT

Merging some suggestions, I found the solution:

if(match(subfield($1,' ',1),'Mr','Ms','Mrs','Dr'),trim(replace($1,subfield($1,' ',1),' ')),$1)

Thank very much for all!