Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

Sorting Data using field names

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 CompaniesTypeAmount
OSAKITIKPI INDUSTRIAL SERVICESLocal23
ENCAPSULATE NIGERIA LIMITEDFederal12
BRIGHT SHARKES & ASSOCIATESLocal43
FRANCIS KOYO & COMPANY.Local45
DJOMAH GLOBAL PETROLEUM SERVICESLocal1
procare global services ltd,Federal23

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.  

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Akpofure,

Try:

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

Regards

Andrew

View solution in original post

3 Replies
effinty2112
Master
Master

Hi Akpofure,

Try:

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

Regards

Andrew

akpofureenughwu
Creator III
Creator III
Author

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
Master
Master

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