Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

Remove specific character from field value

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

 

 

 

 

1 Solution

Accepted Solutions
QFabian
Specialist III
Specialist III

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

 

QFabian

View solution in original post

9 Replies
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
karan_kn
Creator II
Creator II
Author

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 

sunny_talwar

I think @MayilVahanan solution is working

image.png

MayilVahanan

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)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
karan_kn
Creator II
Creator II
Author

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

 

Chanty4u
MVP
MVP

Hi

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;

sub.PNG

karan_kn
Creator II
Creator II
Author

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

karan_kn
Creator II
Creator II
Author

7385359.jpg

Remove LLC, Inc from Company name, attached sample company

QFabian
Specialist III
Specialist III

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

 

QFabian