topic Re: Remove specific character from field value in QlikView

Remove specific character from field value

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

How to remove last string values from Field values (ex: ', Inc', ', inc', 'Inc.', 'inc.', 'Ltd.', 'Plc')

Field1:
Aero Incredible Work Out, Inc
Zero Impress, Ltd.

To change

Aero Incredible Work Out
Zero Impress

Thanks

Re: Remove specific character from field value

MayilVahanan — Wed, 14 Oct 2020 10:41:22 GMT

HI Karan,

Try with SubField function, it will helps you.

=SubField(Field, ',',1)

=SubField('Aero Incredible Work Out, Inc', ',',1) gives Aero Incredible Work Out

Re: Remove specific character from field value

karan_kn — Wed, 14 Oct 2020 11:07:19 GMT

Sorry, I'm getting only 'Aero', if I use the above condition since the 'Incredible' contains 'Inc', we need to remove the last 'Inc ' from the string

Re: Remove specific character from field value

sunny_talwar — Wed, 14 Oct 2020 11:19:57 GMT

I think @MayilVahanan solution is working

Re: Remove specific character from field value

MayilVahanan — Wed, 14 Oct 2020 11:23:47 GMT

Hi Karan,

SubField(Field, ',',1)

SubField is the function helps to separate the words based on the delimiters like comma, space, or any other words.

Ex: Aero Incredible Work Out, Inc <- for this values, we need to fetch the values before comma separator.

SubField(Field, ',',1) gives the expected results.

Suppose, if you have more than 1 comma separator in your values, then use like below

=Left('Aero Incredible, Work, Out, Inc', Len('Aero Incredible, Work, Out, Inc')- Len(SubField('Aero Incredible, Work, Out, Inc', ',',SubStringCount( 'Aero Incredible, Work, Out, Inc',',')+1))-1)

Re: Remove specific character from field value

karan_kn — Wed, 14 Oct 2020 11:53:16 GMT

Sorry If I confused both, I'm not looking to remove only for specific values, have thousands of values like this. I need a solution based on the field. need to eliminate Inc LLC from the field.

Company Name Field
Company Laboratories, Inc.
Company Oil, Inc
Company Oil, Inc.
Company Online, Inc.
Company Orthopaedic Clinic, LLC

New Company Name Field
Company Laboratories
Company Oil
Company Oil
Company Online
Company Orthopaedic Clinic

Re: Remove specific character from field value

Chanty4u — Wed, 14 Oct 2020 12:38:25 GMT

can you try this?

a:
load * Inline [
"Company Name Field"
"Company Laboratories, Inc."
"Company Oil, Inc"
"Company Oil, Inc."
"Company Online, Inc."
"Company Orthopaedic Clinic, LLC "
];

result:
load *,
SubField("Company Name Field",',',-2) as new Resident a;
drop Table a;

exit SCRIPT;

Re: Remove specific character from field value

karan_kn — Wed, 14 Oct 2020 13:21:00 GMT

It's removing text whatever after comma, I required to remove only 'Inc' or 'Inc.' or 'LLC' or ', Inc,' or ', Inc.'

Re: Remove specific character from field value

karan_kn — Wed, 14 Oct 2020 13:24:12 GMT

Remove LLC, Inc from Company name, attached sample company

Re: Remove specific character from field value

QFabian — Wed, 14 Oct 2020 13:56:33 GMT

Hi, try this :

Load

mid(replace(replace(replace(upper([Company Name Field]), 'INC', '') , 'LTD', '') , 'PLC', ''), 1, len([Company Name Field] )-4) as [New Company Name Field]

Resident from