In a filter pane, hide unwanted values AND replace other values using a single expression?

[ogatchell]

To clean values (clean = hide unwanted values and replace other values) from a filter pane (viz., State) I use IF  function to hide the unwanted values and PICK function to replace specific values. Both functions work fine separately in the filter pane  but when combined in a single expression, produce mutually exclusive results; I have also applied the same expression in the script, which results in a blank filter pane. I have  used "&", "and", "or" to combine the functions in a single expression, such as in the expression below. 

Any fixes to this expression or should I take another approach?

=if(Match (State,
'75081',
'94954',
'`',
'674491697 674491697',
'AB',
'AE',
'ALBERTA',
'AU',
'BG',
'BRITISH COLUMBIA',
'British Columbia',
'DM',
'GU',
'JP',
'MANITOBA',
'MY',
'NEW BRUNSWICK',
'NEWFOUNDLAND',
'NEWFOUNDLAND AND LABRADOR',
'NORTHWEST TERRITORIES',
'NOVA SCOTIA',
'NS',
'NUNAVUT',
'ON',
'ONTARIO',
'Ontario',
'PR',
'PRINCE EDWARD ISLAND',
'QUEBEC',
'Quebec',
'Quedgeley',
'R',
'S',
'Santiago',
'Sao Paulo',
'SASKATCHEWAN',
'SHIP',
'TH',
'UK',
'UNKNOWN',
'US',
'VI',
'YUKON TERRITORY',

), Null(), State)

& 

Pick (Match (State, 'California', 'Georgia', 'Maryland', 'Ohio', 'Texas' ), 'CA', 'GA', 'MD','OH','TX', State) 

[Dalton_Ruer · Accepted solution]

Certainly there are times where you may only be the "designer" for the front end and not be allowed to change the source code. In which case you are spot on. It's an ugly bunch of data so you have no choice but to deal with it. 

However, my preference would always be to handle the data cleanup via APPLY MAP. Where you create a table with a MAPPING LOAD and define the values from/to there. Then when loading the table you can use the APPLY MAP function so that you it handles the cleaning. The added benefit is that you have the option to define what you want if you have no match. You can say "Undefined" "No match identified" etc, to handle the future cases when they've really input bogus data that you aren't even aware of yet. 

[ogatchell]

agreed, thanks, Dalton