Hi Friends,
I've created a simple industry classification parameter for Company names, using IF THEN statements. It works perfectly, but I don't know what to do in situations where a single organization falls under two unique industries. For example, CA Inc. can fall under both "Computer Software" and "Computer Hardware." I can't logically compare two strings with AND, so I'm not sure how to proceed.
Does anyone have any insight here? Please let me know..
IF [Member] ="3M" THEN "Chemicals"
ELSEIF [Member] ="AARP" THEN "Membership Organizations"
ELSEIF [Member] ="BNY Mellon" THEN "Banking"
ELSEIF [Member] ="CA Inc" THEN "Computer Software"
ELSEIF [Member] ="CH2M Hill" THEN "Construction"
END
There are many ways to tackle this, but I'll offer two possible solutions - one sort of "quick & dirty", and the 2nd a little more complex.
First possible solution:
Step 1
Modify your case statement to include a list of classifications for each Company. So for CA Inc, you would have:
ELSEIF [Member] ="CA Inc" THEN "Computer Software, Computer Hardware"
Step 2
For filtering, you would create a Parameter called "Selected Classification" and you would add all of the possible classifications as Strings to the parameter, e.g.
Chemicals
Computer Software
Computer Hardware
etc
Step 3
Create a calculated field called "Matches Classification" (assume that the original calculated field is called [Classification]), with the formula:
CONTAINS([Classification], [Selected Classification])
This will return true if the Classification contains the substring Selected Classification - so for example, CA Inc would return True for BOTH "Computer Hardware" and "Computer Software"
Step 4
Add this calculated field as a filter - you should be able to select either Computer Hardware or Computer Software, and see CA Inc.
The other possible solution is handle multiple classifications in the data, such that you would have multiple records for a company that belonged to more than one classification. You could do this for example using data blending, with a secondary data source that had a map of classifications for all companies. You would blend your primary data source with this data source. It would look something like this:
Company Classification
CA Inc Computer Hardware
CA Inc Computer Software
Dow Chemicals
etc.
etc.
You would blend your primary source to this source on company name, and then filter using the Classification field.
Hope this helps,
-Steve
