Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
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