Smarter Wildcard Match Filtering

Tableau method for wildcard filtering using multiple search terms.

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.

The Solution

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.

wildcard match filtering oneThen 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”.

wildcard match filtering two

Using the James Bond film series as a dataset, I search for “die live” and see what is returned.

ssnap3

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.

  1. A secondary list which shows partial matches (e.g. at least one of your search terms is found)
  2. 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”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s