topic Re: Sorting Data using field names in QlikView

Sorting Data using field names

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

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

ENCAPSULATE NIGERIA LIMITED

Federal

BRIGHT SHARKES & ASSOCIATES

Local

FRANCIS KOYO & COMPANY.

Local

DJOMAH GLOBAL PETROLEUM SERVICES

Local

procare global services ltd,

Federal

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.

Re: Sorting Data using field names

effinty2112 — Wed, 01 Nov 2017 09:32:45 GMT

Hi Akpofure,

Try:

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

Regards

Andrew

Re: Sorting Data using field names

akpofureenughwu — Wed, 01 Nov 2017 11:36:30 GMT

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

Re: Sorting Data using field names

effinty2112 — Wed, 01 Nov 2017 11:55:10 GMT

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