Sorting Data using field names

[akpofureenughwu]

Hello

I have a set of data that are pretty dirty. The data is a list of companies. I need to sort them into two specific groups.

Name of Companies	Type	Amount
OSAKITIKPI INDUSTRIAL SERVICES	Local	23
ENCAPSULATE NIGERIA LIMITED	Federal	12
BRIGHT SHARKES & ASSOCIATES	Local	43
FRANCIS KOYO & COMPANY.	Local	45
DJOMAH GLOBAL PETROLEUM SERVICES	Local	1
procare global services ltd,	Federal	23

In the table above, I need to sort the company based on their registration mode (Limited or Enterprise).  I want all 'Limited and ltd'  to Federal and others States.  

[effinty2112 · Accepted solution]

Hi Akpofure,

Try:

IF(Wildmatch(UPPER([Name of Companies]),'*LIMITED*','*LTD*'),'Federal', 'States')

Regards

Andrew

[akpofureenughwu]

effinty2112‌

Thank you for your prompt response. It looks promising...

How can I build a chart to show only companies under the  IF(Wildmatch(UPPER([Name of Companies]),'*LIMITED*','*LTD*'),'Federal', 'States') expression?

Regards

[effinty2112]

Hi Akpofure,

There's a couple of approaches. You can add this piece of set analysis to all the expressions

{$<Customer = {'*ltd','*limited'}>}

or instead you can use this dimension which would leave you free to add simple expressions without having to remember to include the set analysis in each one:

=Aggr(if(Wildmatch(Upper([Name of Companies]),'*LTD','*LIMITED'),[Name of Companies]),[Name of Companies])

in the dimensions tab check the option to Suppress When Value Is Null.

I've dropped the second * from my expressions so that we are only looking at the end of a company name and won't now pick a company if the name has 'ltd' in the middle of the name.

Good luck

Andrew