6 Replies Latest reply on May 26, 2013 2:51 PM by David Vale

    Wild card search on two dimensions

    David Vale

      I have two dimensions Registration and Fleet Number. I'd like to have a wild card search both of these dimensions and display the result. Some vehicle's would have both a registration number and a fleet number, but some vehicles will have one but not the other.

       

      So if I am searching for a vehicle with only a fleet number I can use the same wildcard search box as if I was searching for a registration number.

       

      Thanks for any help

      David.

        • 1. Re: Wild card search on two dimensions
          Dylan Snyder

          David, here's a quick example.

           

          I added customer name/item combinations to my sheet and created a parameter called "Match". The calculated field "ParamFilter" below checks to see if the customer name or item fields contain your string.

           

           

          if (

               (contains([Customer Name],[Match]) == true) OR

               (contains([Item],[Match]) == true)

              )

          then 1

          else 0 end

          • 2. Re: Wild card search on two dimensions
            David Vale

            Hi Dylan,

             

            Thanks for the quick response, it worked perfectly.

             

            Most helpful,

            Cheers, David.

            • 3. Re: Wild card search on two dimensions
              David Vale

              Hi Dylan,

               

              I have a subsequent question relating to the solution you provided. How do I restrict the search to the entire contents of the dimension. For example I search for a fleet number 336, but want to ignore registrations with FTG336?

               

              Thanks,

              David.

              • 4. Re: Wild card search on two dimensions
                Dylan Snyder

                I don't have Tableau in front of me at the moment, but what I'm thinking is that you should create another parameter called, say, SearchType, with the values of "exact" and "wildcard" and change your ParamFilter logic to what's below. You might not be able to copy and paste this exactly (not sure about the curly braces for the blocks under the "if ([SearchType]..." statements), but this should give you some idea of what I'm trying to do

                 

                //if search type is "wildcard", check that the search string is found

                //in either field

                if ([SearchType] == "wildcard")

                then {

                if (

                     (contains([Customer Name],[Match]) == true) OR

                     (contains([Item],[Match]) == true)

                    } then 1

                }

                //if search type is "exact", check that either field matches exactly

                //lower() makes sure there is a match regardless of letter case

                else if ([SearchType] == "exact")

                then {

                            if (

                          (lower([Customer Name]) == lower([Match])) OR

                          (lower([Item]) == lower([Match]))

                         ) then 1

                    }

                else 0

                end

                • 5. Re: Wild card search on two dimensions
                  Dylan Snyder

                  David, this is the code I got to work for the ParamFilter function with the additional parameter. I also attached an updated workbook.

                   

                  if([SearchType] == "wildcard")

                  then

                      if (

                           (contains([Customer Name],[Match]) == true) OR

                           (contains([Item],[Match]) == true)

                          )

                      then 1

                      else 0

                      end

                  elseif([SearchType] == "exact")

                  then

                      if (

                          (lower([Customer Name]) == lower([Match])) OR

                          (lower([Item]) == lower([Match]))

                          )

                      then 1

                      else 0

                      end

                  else 0

                  end

                  1 of 1 people found this helpful
                  • 6. Re: Wild card search on two dimensions
                    David Vale

                    Hi Dylan,

                     

                    Once again you've come up trumps, worked perfectly. Now I get the results that I wanted rather than the shot gun (hundreds) results I was getting.

                     

                    Thanks for your help, saved me hours of work and helped improve a process that'd weve been struggling with.

                     

                    David.