Absolute and partial match filtering

In my previous post about Smarter Wildcard Matching, I showed you how to use a Tableau parameter to improve the capabilities of search filtering in large sets of data. In this post I will show you how to add a partial match to this, where your data matches one or more of your search terms but not all of them.

The Solution

Remember the calculated field for the original wildcard match was

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

Well now we need to add another section which replaces the ANDs with ORs:

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'

ELSEIF (CONTAINS(LOWER([Film]),
LOWER(TRIM( SPLIT( [Wildcard Match], " ", 1 ) )))
AND LEN(TRIM( SPLIT( [Wildcard Match], " ", 1 ) ))0)
OR
(CONTAINS(LOWER([Film]),
LOWER(TRIM( SPLIT( [Wildcard Match], " ", 2 ) )))
AND LEN(TRIM( SPLIT( [Wildcard Match], " ", 2 ) ))0)
OR
(CONTAINS(LOWER([Film]),
LOWER(TRIM( SPLIT( [Wildcard Match], " ", 3 ) )))
AND LEN(TRIM( SPLIT( [Wildcard Match], " ", 3 ) ))0)
OR
(CONTAINS(LOWER([Film]),
LOWER(TRIM( SPLIT( [Wildcard Match], " ", 4 ) )))
AND LEN(TRIM( SPLIT( [Wildcard Match], " ", 4 ) ))0)

THEN 'b) partial match'

ELSE 'c) no match' END

This will return ‘a) match’ if all your search terms are found in the data, and it will return ‘b) partial match’ if it matches at least one but not all.

Note I need the LEN statement to search each SPLIT of the wildcard, because if I am not using it and I only search for, say, two different terms, then the 3rd and 4th splits will be blank, and Tableau will claim that that is a match against every row of data. Therefore we check the length of each split is greater than 0 before comparing it to the data being searched.

To check its worked I try searching in my data with the same search as before.snap4As you can see I’ve still got Live and Let Die as a match, as this contains both my search terms. But now I have three partial matches as well, each of which contain one of the search terms.

Next I will show you how to build in an exclusion wildcard. For example if I wanted films which contain the words “die”, “live” but not “another”.

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