4 Replies Latest reply on Apr 16, 2018 1:46 PM by Monika Wright

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