Tableau’s Wildcard Match and Custom Value List filters are all well and good but they are limited. Both of them require that your search terms are in the correct order and the Custom Value List makes you click each individual item you want to include. For example, searching for “A4 Paper” would not return the [Product Name] “Paper A4”. This is an especially big problem if your field contains thousands of different values with little or no hierarchy to make your search easier.
This is where this Tableau trick can help. It allows you to search for multiple strings in any order, and Tableau will return a list of all items that contain all of those strings.
So how is it done? It works by using a custom string parameter to search, and a calculated field which splits this search into the component terms, before checking whether each search term is contained within the field you want to filter.
First create a string parameter value called “Wildcard Match” with any value allowed but the default set to blank.
Then create a calculated field which splits the parameter up to search through your field values (in this example [Film]).
IF CONTAINS(LOWER([Film]), LOWER( SPLIT( [Wildcard Match], " ", 1 ) )) AND CONTAINS(LOWER([Film]), LOWER( SPLIT( [Wildcard Match], " ", 2 ) )) AND CONTAINS(LOWER([Film]), LOWER( SPLIT( [Wildcard Match], " ", 3 ) )) AND CONTAINS(LOWER([Film]), LOWER( SPLIT( [Wildcard Match], " ", 4 ) )) THEN 'a) match' ELSE 'b) no match' END
The field works using the SPLIT function to take whatever you’ve put in your search and make them separate search terms (for example SPLIT(“The Spy Who Loved Me”,” “,5) = “Me”. We lower case the whole thing and see if it is contained within our [Product Name] field (noting this has also been lower cased). I’ve used four splits, which allows the user to search for up to four search terms in one query, but you can add in as many as you want easily enough.
Then to test if it is working, drag [Films] into rows, and your calculated field into filters, selecting “a) match”.
Using the James Bond film series as a dataset, I search for “die live” and see what is returned.
Notice how it doesn’t matter that I’ve written the terms in a different order.
This means I can now search for different terms, in any order and in any case, and if I’m not sure on the spelling I can type in a few letters instead to help narrow the search.
Taking it further
In my next posts I’ll show you two ways of making this search even more effective.
- A secondary list which shows partial matches (e.g. at least one of your search terms is found)
- A second parameter which allows you to exclude products based on your search (e.g. if you wanted films which contained “live”, “let” but not “die”)