6 Replies Latest reply on May 19, 2017 4:50 AM by Dianna Santo

    Wildcard Search Multiple Name

    emmanuel.owusu

      I am working on a report and business users want to be able to search multiple names. The only issue is the search has to be a wildcard. For example

      if I copy and paste Law, John, Brad into the search bar it should return me all customers with those letters in their name such as John, Johnathan, Lawrence, Lawson, Bradford, and Brad.

      I have attached a workbook where I am able to search by just one value but unable to search for comma separated values., Can anyone please assist.

       

        Thank you

        • 1. Re: Wildcard Search Multiple Name
          lei.chen.0

          Hello Emmanuel,

           

          Please try,

           

          (FIND([Name Search Parameter],",")=0 AND CONTAINS([Customer Name],TRIM([Name Search Parameter]))) OR

          (FIND([Name Search Parameter],",")<>0 AND (

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",1))) OR

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",2))) OR

          (FINDNTH([Name Search Parameter],",",2)<>0 AND

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",3)))) OR

          (FINDNTH([Name Search Parameter],",",3)<>0 AND

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",4)))) OR

          (FINDNTH([Name Search Parameter],",",4)<>0 AND

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",5)))) OR

          (FINDNTH([Name Search Parameter],",",5)<>0 AND

          CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",6))))))

           

          Here are my assumptions,

          1) the delimiter for keywords is ","

          2) the max number of "," is five. Since there is no loop statement, more manual work is necessary.

           

           

          Regards.

          2 of 2 people found this helpful
          • 2. Re: Wildcard Search Multiple Name
            Rajeev Pandey

            Hi,

             

            Please read the below blog which will definitely solve your issue.

            Multiple value wildcard search filter in Tableau - The Information Lab

            1 of 1 people found this helpful
            • 3. Re: Wildcard Search Multiple Name
              mafalda.santos

              Hi Lei,

               

              I have a doubt about your answer to Emmanuel. Your solution works for me but the only problem is that my [Customer Name]  is a number which datatype is a string. But when I type in '1,5' on [Name Search Parameter] , Customers Name with number 1 and 5 not only appear but also the Customers Name with 1 and 5 on their Name (like 129,1,152,5) and I only need to see string 1 and 5.

               

              Can you help me on this ?

               

              Regards,

               

              Mafalda

              • 4. Re: Wildcard Search Multiple Name
                lei.chen.0

                Hello Mafalda,

                 

                Thanks for searching and viewing this old post !

                 

                Mafalda Santos wrote:

                 

                Customers Name with number 1 and 5 not only appear but also the Customers Name with 1 and 5 on their Name (like 129,1,152,5) and I only need to see string 1 and 5.

                 

                From your example, I see that your search requirement is not

                either *A* or  *B*

                but

                either A or B

                 

                So just use = (equal) in stead of CONTAINS function in the calculation.

                 

                Continuing using Emmanuel's data as sample, I use [Order ID] for search field, and the new calculation will be,

                 

                (FIND([Name Search Parameter],",")=0 AND STR([Order ID])=TRIM([Name Search Parameter])) OR

                (

                FIND([Name Search Parameter],",")<>0 AND

                (

                (STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",1)) AND TRIM(SPLIT([Name Search Parameter],",",1))<>"") OR

                (STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",2)) AND TRIM(SPLIT([Name Search Parameter],",",2))<>"") OR

                (FINDNTH([Name Search Parameter],",",2)<>0 AND STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",3)) AND TRIM(SPLIT([Name Search Parameter],",",3))<>"") OR

                (FINDNTH([Name Search Parameter],",",3)<>0 AND STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",4)) AND TRIM(SPLIT([Name Search Parameter],",",4))<>"") OR

                (FINDNTH([Name Search Parameter],",",4)<>0 AND STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",5)) AND TRIM(SPLIT([Name Search Parameter],",",5))<>"") OR

                (FINDNTH([Name Search Parameter],",",5)<>0 AND STR([Order ID])=TRIM(SPLIT([Name Search Parameter],",",6)) AND TRIM(SPLIT([Name Search Parameter],",",6))<>"")

                )

                )

                 

                Furthermore, I have to correct my previous caculation for Emmanuel into,

                 

                (FIND([Name Search Parameter],",")=0 AND CONTAINS([Customer Name],TRIM([Name Search Parameter]))) OR

                (

                FIND([Name Search Parameter],",")<>0 AND

                (

                (CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",1))) AND TRIM(SPLIT([Name Search Parameter],",",1))<>"") OR

                (CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",2))) AND TRIM(SPLIT([Name Search Parameter],",",2))<>"") OR

                (FINDNTH([Name Search Parameter],",",2)<>0 AND CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",3))) AND TRIM(SPLIT([Name Search Parameter],",",3))<>"") OR

                (FINDNTH([Name Search Parameter],",",3)<>0 AND CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",4))) AND TRIM(SPLIT([Name Search Parameter],",",4))<>"") OR

                (FINDNTH([Name Search Parameter],",",4)<>0 AND CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",5))) AND TRIM(SPLIT([Name Search Parameter],",",5))<>"") OR

                (FINDNTH([Name Search Parameter],",",5)<>0 AND CONTAINS([Customer Name], TRIM(SPLIT([Name Search Parameter],",",6))) AND TRIM(SPLIT([Name Search Parameter],",",6))<>"")

                )

                )

                 

                The reason is when string like "keyword1," or "keyword1,keyword2," is inputted, search result will return all the rows.

                 

                 

                Regards

                Lei

                1 of 1 people found this helpful
                • 5. Re: Wildcard Search Multiple Name
                  mafalda.santos

                  Many thanks Lei ! It works well

                   

                  Regards

                   

                  Mafalda

                  • 6. Re: Wildcard Search Multiple Name
                    Dianna Santo

                    We had searched for weeks for a solution that works and tried many that were suggested to no avail. Finally we found a solution on the web that is working for us!

                    This code was essentially copied from a post made by Daniel Vincent, here is the link to his post:

                    https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.tableau.com%2Fthread%2F193895%3Fstart%3D0%26tstart%3D0&data=01%7C01%7Cdianna.santo%40tax.state.oh.us%7C40b0eff0cb114f4dbfd108d49e48ecf7%7C50f8fcc494d84f0784eb36ed57c7c8a2%7C0&sdata=kfgbf1q7zlhsSaav8EzHb7Z4vKRRhXii4ZoUwmuJbvY%3D&reserved=0

                     

                    Business requested 20 fields to search dynamically by alone and/or in combination of the other search fields:

                    • Ability to use more than 1 at a time if needed
                    • Ability to enter up to 1000 values (separated by comma)

                     

                    1. We created a New Custom SQL Query and did an inner join to our main data source to create the search parameters we needed.
                    2. We used the "Insert Parameter" at the bottom of the SQL box and selected "Create New Parameter".
                    3. We named each Parameter as "Multi <FIELD NAME> Search box" followed by like '%' || FIELD NAME|| '%' and FIELD NAME<> ''.

                     

                    I have changed all the various fields they wanted to search by to "FIELD NAME" below to protect the information in my data source. But we have 20 search parameters that are working just fine! Alone and in combination, understanding its built with the "OR" clause (not "AND").

                    As long as it just one item in the search parameter values entered it will come back in the results.\

                     

                    Here is a sample of the SQL:

                     

                    select *

                    from <name of database>

                    where (<Parameters.Multi FIELD NAME Search box> like '%' || FIELD NAME|| '%' and FIELD NAME<> '')

                    or (<Parameters.Multi FIELD NAME Search Box> like '%' || FIELD NAME|| '%' and FIELD NAME<> '')

                    or (<Parameters.Multi FIELD NAME Search Box> like '%' || FIELD NAME|| '%' and FIELD NAME<> '')

                     

                    Kudos to Daniel Vincent who saved the day!