Using Text Box to enter list of values for filtering

[#1]

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:

1. Define a separator for the values in the list.
2. Take care of certain exceptions, such as when the Text Box is empty.
3. Deal with blank spaces that could appear at the beginning and/or at the end of typed zip code values.
4. Deal with mixed upper and lower case characters to support all types of zip codes around the world that could contain letters.
5. Refine our requirements – for example: what happens if an incomplete zip code is typed, such as 234? Should we select all zip codes that contain 234 or should it be a full match? What about the opposite – user types 12345-678 and the table contains 12345 – should they match?

Here is what we have decided:

1. Using comma as the separator would work just fine, as comma is not a character used in zip codes.
2. If the Text Box is left empty, then it should have the same behavior as Visual Analytics: no filter is applied.
3. Leading and training blanks for each of the values should be ignored, as they are not part of the zip code values being searched.
4. We would standardize in upper case characters only.
5. Typed values and values stored in the table should match entirely, so 12345 would not match 12345-678 and vice-versa.

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:

UpCase(RemoveBlanks('Parameter'p, _All_))

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:

• “zipcodeA,12345,zipcodeB” Contains “12345-678” ➡️ returns FALSE (not a match: correct)
• “zipcodeA,12345-678,zipcodeB” Contains “12345” ➡️ returns TRUE (a match: incorrect)

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:

• 12345-
• 123
• 45-6
• -678
• -
• 7
• etc…

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:

• “zipcodeA,12345, zipcodeB” Contains “,12345-678,” ➡️ returns FALSE (not a match: correct)
• “zipcodeA,12345-678, zipcodeB” Contains “,12345,” ➡️ returns FALSE (not a match: correct)

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:

• “12345-678” Contains “,12345-678,” ➡️ returns FALSE (not a match: incorrect)

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_)), 
                        ','
                        )
            )

• “,12345-678,zipcodeB,” Contains “,12345-678,” ➡️ returns TRUE (a match: correct)
• “,zipcodeA,12345-678,” Contains “,12345-678,” ➡️ returns TRUE (a match: correct)
• “,12345-678,” Contains “,12345-678,” ➡️ returns TRUE (a match: correct)

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:

Figure 01 - Example with car make

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! ✔️