6 Replies Latest reply on Jul 7, 2018 3:37 AM by Simon Runc

    Calculation for multiple value wild card search/filter - map and bubble charts

    Monika Wright

      Please help! Read several threads to locate this answer and have tried a few but I still have nothing. I need to create a filter for a multiple query search. For instance, I would like to search for the following mulatto, complexion, and woman. I have a bubble chart workbook attached (would like to replicate with a filled map visualization as well).

        • 1. Re: Calculation for multiple value wild card search/filter - map and bubble charts
          Simon Runc

          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

          • 2. Re: Calculation for multiple value wild card search/filter - map and bubble charts
            Monika Wright

            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

              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!!)

              • 5. Re: Calculation for multiple value wild card search/filter - map and bubble charts
                Monika Wright

                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

                  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 end-user too!).