
1. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Simon Runc Apr 14, 2018 4:04 AM (in response to Monika Wright)hi Monika,
One way we can do this is with a parameter and the SPLIT function.
I created a free text string parameter, where the multi search is done via comma delimiting
I then set up 3 calculations, using SPLIT, to parse out the 3 terms (if you want the user to be allowed more you just need to set up that many)
[Transcript Search Term 1]
SPLIT([Trascript Search (comma delimit)],',',1)
[Transcript Search Term 2]
SPLIT([Trascript Search (comma delimit)],',',2)
[Transcript Search Term 3]
SPLIT([Trascript Search (comma delimit)],',',3)
and then I use these to create a T/F filter (I've also added in handlers so it's not case sensitive and also if someone only puts in 1 or 2 search terms). I've also used the OR, but you can change to AND if you want that behaviour
[Transcript Search Filter]
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 1],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 2],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 3],'')))
Bring this onto the filter shelf and set to true.
Hope that helps and makes sense

Multi Terms Wild Card Search.twbx 771.1 KB


2. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Monika Wright Apr 14, 2018 10:19 AM (in response to Simon Runc)Thank you! This search query works great for exactly what I asked  searching for multiple terms. Unfortunately, I should have been more specific by stating that I wanted to search for multiple terms (for example, 3) but still be able to search for a single term. Whenever I search for a single term, it returns all the records in a bubble instead of those within the search parameter. For example: I did a one term search for pantaloons. Carrabus County has a total of 57 records with only 22 records that contain pantaloons. But the search returned all 57 records.
Also, is it possible to perform an AND and OR query (boolean) within a single search?

3. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Simon Runc Apr 15, 2018 7:35 AM (in response to Monika Wright)hi Monika,
So we can just add a bit more logic to the formulas to handle different number of search terms.
First I've added this calculation to determine the number of search terms
[Transcript Search  No of Terms]
LEN([Trascript Search (comma delimit)])

LEN(REPLACE([Trascript Search (comma delimit)],',',''))
+
1
and then we can use this as an extra test
[Transcript Search Filter]
IF LEN([Trascript Search (comma delimit)]) = 0 THEN TRUE
ELSEIF [Transcript Search  No of Terms] = 1 THEN
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Trascript Search (comma delimit)],'')))
ELSEIF [Transcript Search  No of Terms] = 2 THEN
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 1],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 2],'')))
ELSEIF [Transcript Search  No of Terms] = 3 THEN
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 1],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 2],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 3],'')))
END
On the other post...I can exercise one of my Tableau Ambassador powers and delete this (in fact this is the only extra power I have!!)

Multi Terms Wild Card Search.twbx 822.7 KB


4. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Monika Wright Apr 16, 2018 1:46 PM (in response to Simon Runc)Thank you Simon! Works like a charm!!!

5. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Monika Wright Jul 6, 2018 8:12 PM (in response to Simon Runc)Hi Simon,
Is it possible to add two more search term parameters (for a total of 5)? And is it possible to change the calculation to allow someone to search by using (and), (or), and the widlcard (*) search parameters?
I have been trying to make these changes but I have been unsuccessful?

6. Re: Calculation for multiple value wild card search/filter  map and bubble charts
Simon Runc Jul 7, 2018 3:37 AM (in response to Monika Wright)hi Monika,
So to increase the number of search terms, you can just extend the same logic (in the attached I've added a 4th)
[Transcript Search Term 4]
SPLIT([Transcript Search (comma delimit)],',',4)
and we can add this to filter
[Transcript Search Filter OR]
IF LEN([Transcript Search (comma delimit)]) = 0 THEN TRUE
ELSEIF
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 1],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 2],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 3],'')))
OR
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 4],'')))
THEN TRUE
END
btw I've come up with a simpler version of this formula so we don't need to check the number of terms, apart from the case where there is no search term entered into the parameter.
With regards AND or OR, yes we can add a parameter so the user can select AND or OR, and then I've made 2 versions of the filter, one for OR and one for AND (above is the OR one) and the AND is
[Transcript Search Filter AND]
IF LEN([Transcript Search (comma delimit)]) = 0 THEN TRUE
ELSEIF
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 1],'')))
AND
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 2],'')))
AND
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 3],'')))
AND
CONTAINS(UPPER([Transcript]),UPPER(IFNULL([Transcript Search Term 4],'')))
THEN TRUE
END
We can then make a further formula, to pick up the right one and use that in the filter
[Transcript Search Filter AND/OR]
IF [Transcript Search AND or OR] = 1 THEN [Transcript Search Filter AND]
ELSE [Transcript Search Filter OR]
END
btw in my AND/OR selection parameter 1 = AND and 2 = OR (I just prefer using Integer parameters and Alias the "human readable" name)
With regards WildCard...as we're using CONTAINS these are already Wildcard searches (Aug or August would both return transcripts with the string "august"...the aug version could also return others, say "Augmented").
If you wanted to make it Search Engine Like, where you'd use "xxxxx" to determine an exact search and * to determine wildcard; while possible (I think) it would take quite a bit of logic as we'd need to check each of the 5 entries, and look if they were enclosed in " " or contained a * to determine if we were to use a CONTAINS or Equals (we'd have to assume that an entire word, the " " case, had spaces at either end, or was the last word). So although, logically possible, it would require a lot of formulas and logic, especially with an AND and OR version (and might not scale very well). I'd see how you get on with the 5 terms and the AND/OR, as something even more refined would soon get very complicated (and possibly the enduser too!).