Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Akpofure,
Try:
IF(Wildmatch(UPPER([Name of Companies]),'*LIMITED*','*LTD*'),'Federal', 'States')
Regards
Andrew
Hi Akpofure,
Try:
IF(Wildmatch(UPPER([Name of Companies]),'*LIMITED*','*LTD*'),'Federal', 'States')
Regards
Andrew
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
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