Chart expression question

fors_gregg — Mon, 08 Oct 2018 16:15:08 GMT

In displaying data in a chart, I'm labeling/grouping various division names per another convention since, for example, "CONSTRUCTION" is composed of 8 distinct clients:

=if(DIVISION_NAME = 'Construction Industry Licensing Board', 'CONSTRUCTION',

(if(DIVISION_NAME = 'Condos, Coops, Timeshares, Mult-Site TS', 'CTMH/CAMS',

(if(DIVISION_NAME = 'Board of Cosmetology', 'PROFESSIONS',

(if(DIVISION_NAME = 'Electrical Contractors Licensing Board', 'CONSTRUCTION',

(if(DIVISION_NAME = 'Div of Alcoholic Beverages & Tobacco', 'AB&T',

(if(DIVISION_NAME = 'Division of Hotels and Restaurants', 'H&R',

(if(DIVISION_NAME = 'Barbers Board', 'PROFESSIONS',

(if(DIVISION_NAME = 'Division of Pari-Mutuel Wagering', 'PMW',

(if(DIVISION_NAME = 'Division of Pari-Mutuel Wagering - Slots','PMW',

(if(DIVISION_NAME = 'Division of Real Estate', 'REAL ESTATE',

(if(DIVISION_NAME = 'Board of Pilot Commissioners','PROFESSIONS',

(if(DIVISION_NAME = 'Board of Veterinary Medicine','PROFESSIONS',

(if(DIVISION_NAME = 'Community Association Managers','CTMH/CAMS',

(if(DIVISION_NAME = 'Florida Board of Auctioneers','PROFESSIONS',

(if(DIVISION_NAME = 'Office of Talent Agents','PROFESSIONS',

(if(DIVISION_NAME = 'Office of Athlete Agents','PROFESSIONS',

(if(DIVISION_NAME = 'Florida State Boxing Commission','PROFESSIONS',

(if(DIVISION_NAME = 'Board of Employee Leasing Companies','PROFESSIONS',

(if(DIVISION_NAME = 'Board of Accountancy','CPA',

(if(DIVISION_NAME = 'Florida Real Estate Appraisal Bd','REAL ESTATE',

(if(DIVISION_NAME = 'Labor Organization','CHILD/FARM LABOR',

(if(DIVISION_NAME = 'Farm Labor','CHILD/FARM LABOR',

(if(DIVISION_NAME = 'Child Labor Board','CHILD/FARM LABOR',

(if(DIVISION_NAME = 'Florida Drugs, Devices and Cosmetics', 'DDC',

(if(DIVISION_NAME = 'Mobile Homes','CTMH/CAMS',

(if(DIVISION_NAME = 'Bureau of Elevator Safety','H&R',

(if(DIVISION_NAME = 'Home Inspectors Licensing Program','CONSTRUCTION',

(if(DIVISION_NAME = 'Board of Professional Geologists','CONSTRUCTION',

(if(DIVISION_NAME = 'Mold-Related Services Licensing Program','CONSTRUCTION',

(if(DIVISION_NAME = 'Asbestos Licensing Unit','CONSTRUCTION',

(if(DIVISION_NAME = 'Board of Landscape Architecture','CONSTRUCTION',

(if(DIVISION_NAME = 'Yacht and Ship Brokers','CTMH/CAMS',

(if(DIVISION_NAME = 'Land Sales','CTMH/CAMS',

(if(DIVISION_NAME = 'Building Code Administrators & Inspector','CONSTRUCTION',

(if(match(DIVISION_NAME,'Office of the Secretary','Division of Regulation',

'Other Entities','Funeral Directors and Embalmers','OGC Miscellaneous',

'State Attorney''s Office','Board of Architecture & Interior Design',

'Board of Professional Engineers','Board of Prof Surveyors & Mappers')=0,DIVISION_NAME)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

The last piece is only to exclude these other clients I don't want to count/display.

My problem is this: I also need to display a "Division Name" of "UNLICENSED". These would be the same client names as those comprising "CONSTRUCTION", but which have one of several 'unlicensed' codes.

I've tried to add the following to the formula, but i keep getting an error.

(if(match(DIVISION_NAME,'Construction Industry Licensing Board',
'Electrical Contractors Licensing Board',

'Home Inspectors Licensing Program','Board of Professional Geologists',

'Mold-Related Services Licensing Program','Asbestos Licensing Unit',

'Board of Landscape Architecture','Building Code Administrators & Inspector')

and match(CLASS_CODE, 'UNLC','UNLA','UNLI','UNEO','RUNL'),'UNLICENSED',

Thanks for any help...

Fors

Re: Chart expression question

dplr-rn — Mon, 08 Oct 2018 19:47:36 GMT

Such a big if condition will be very slow.

Why dont you move the grouping to the script?

use a map for your association and applymap with a default value for the exceptions

topic Chart expression question in QlikView

Chart expression question

Re: Chart expression question