A customer once asked me how to use a Text Box to filter multiple values. He wanted to type a list of zip codes and use that list to filter other objects in the report. If he had Viya 2020.1.1 (December 2020) or later, I could have referred him to the search feature for List objects, but he was running Viya 3.5, so we had to be creative and develop our own solution.
The solution was obviously an advanced filter applied to the object to be filtered, and the Contains operator was the fist thing that came up to my mind. The syntax for Contains is the following:
<string1> Contains <string2>
In our case, <string1> would be the list of values typed in the Text Box, which could be captured in a parameter, and <string2> would be the data item containing the zip codes in the source table. In other words, the basic expression was:
'Parameter'p Contains 'ZipCode'n
This of course was the solution at a high level, but it didn’t take too long to realize we would need to:
Here is what we have decided:
To test if the input list of zip codes (a character parameter) is empty, we used the IsSet operator, and to ignore the filter if it’s empty, we used the expression 1 = 1 (which returns TRUE, as Visual Analytics doesn’t have a Boolean type):
IF ( 'Parameter'p IsSet ) RETURN (<expression>) ELSE ( 1 = 1 )
Now we needed to work on the <expression>. To remove blanks, we used the RemoveBlanks operator and to standardize the input, we applied the UpCase operator:
Our <expression> then became:
UpCase(RemoveBlanks('Parameter'p, _All_)) Contains UpCase(RemoveBlanks('ZipCode'n, _All_))
Observe that the same transformations were applied to both sides, just to make sure we are comparing apples to apples.
For the full match of values, the operator Contains naturally takes care of cases where the user types 12345 on the list of values and the table contains a zip code like 12345-678 (it’s a no match as defined), but the opposite is not true:
If the user types 12345-678 in the Text Box, it would match zip code 12345, and potentially any other subset of the input value, such as those bad zip codes:
To guarantee the full match of what was being typed, we had to search for the entire value, and an easy way to do that was to include characters to mark the beginning and the end of the values in the search string. Well, it turned out that all values were already separated by comma in the input text, so it naturally became the character to indicate start and end of values. All we had to do was to include the separator in the expression, by concatenating a comma before and after the zip codes stored in the table, using the operator Concatenate twice:
Concatenate( ',', Concatenate( UpCase(RemoveBlanks('ZipCode'n, _All_)), ',' ) )
After concatenating comma before and after:
This seemed all we had to do, except for one thing: if user types 12345-678 as the first or the last element on the search list (or both, if the list contains only one element), it will not contain the separator character, and valid zip code values will not be found:
To fix that we needed to append a comma in the beginning and at the end of the entire typed input parameter string, exactly as we did before with the zip codes stored in the table:
Concatenate( ',', Concatenate( UpCase(RemoveBlanks('Parameter'p, _All_)), ',' ) )
Putting everything together, this is how the filter expression looked like:
IF ( 'Parameter'p IsSet ) RETURN ( Concatenate(',', Concatenate(UpCase(RemoveBlanks('Parameter'p, _All_)), ',')) Contains Concatenate(',', Concatenate(UpCase(RemoveBlanks('ZipCode'n, _All_)), ',')) ) ELSE ( 1 = 1 )
In our case, zip codes were already stored as characters in the source table, but if that was not the case, we could simply apply the Format operator to transform them into characters, before the other character operators were used in the expression.
Obviously, as long as the requirements remain the same, this expression can be leveraged for other use cases, such as searching for car makes:
Note that Mercedes did not show up in the bar chart because it’s stored as Mercedes-Benz in the table. In another hand, Nissan is a valid car make but Nissan SUV is not, so it was not returned. Also, because the implemented filter is not case sensitive, Honda was displayed as expected, even if typed in a strange way. Non-existing makes such as xyz, empty values, and extra blank spaces didn’t affect the results. Check mark! ✔️
SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.