11 Replies Latest reply on May 6, 2014 7:49 AM by Antonio Willybiro

    Keyword search in a text string

    Christopher Garrabrant, Ph.D.

      I am looking for the code to create a keyword search based upon a text string similar to Excel, e.g.,

      =IF(ISERROR(SEARCH("*wrist*",U2,1)),"","Wrist")

       

      On top of this I'd like to create this using a nested statement.

       

      Any support would be appreciated.

       

      Thank you,

       

      Chris Garrabrant

        • 1. Re: Keyword search in a text string
          Russell Christopher

          Doctor Garrabrant -

           

          What is your ultimate goal? Are you simply trying to filter a text string by a keyword, or do something more interesting like get the count of a specific word from a string?

          • 2. Re: Keyword search in a text string
            Christopher Garrabrant, Ph.D.

            The latter. I am trying to develop categories based upon key words that are in the string text.

             

            Thank you.

            • 3. Re: Keyword search in a text string
              Russell Christopher

              Based on your original expression, I'm assuming you only need to know if the word in question exists vs. getting the count of a specific word in a string.

               

              That being said, try this:

               

              FIND( [The Text], [My Keyword], 1) > 0

               

              It returns TRUE or FALSE based on whether the keyword specified in the [My Keyword] parameter is found.

               

              Sample attached.

               

              Good luck!

              • 4. Re: Keyword search in a text string
                Antonio Willybiro

                Hi, I have the same type of question. I need to find out how many time a specific keyword can be found within a text.

                 

                any clue?

                 

                thanks

                Antonio

                • 5. Re: Keyword search in a text string
                  Zac Hilbert

                  I don't think you will be able to get a count unless your data is a live connection to a database that supports that feature. If so, you might be able to use a Pass Through function. Even then, unless your database has a built-in function for doing this, you're probably out of luck.

                   

                  If you only need to test for small counts of occurrences (say countable on your fingers), you can do one of these pseudo-recursive calculated fields (but it's ugly):

                   

                  if find([Item],'a') > 0

                  then

                      if find([Item],'a',find([Item],'a')+len('a')) > 0

                      then

                          if find([Item],'a',find([Item],'a',find([Item],'a')+len('a'))+len('a')) > 0

                          then

                              if find([Item],'a',find([Item],'a',find([Item],'a',find([Item],'a')+len('a'))+len('a'))+len('a')) > 0

                              then "4 or more"

                              else "3"

                              end

                          else "2"

                          end

                      else "1"

                      end

                  else "None"

                  end

                  • 6. Re: Keyword search in a text string
                    Antonio Willybiro

                    I see thanks.

                     

                    Unfortunately the keywords may be hundreds of time in the entire text column I am looking into.

                     

                    For now I took this task offline. doing a basic copy paste into MS Word then doing a search for the keywords.

                    • 7. Re: Keyword search in a text string
                      Tom W

                      Antonio Willybiro wrote:

                       

                      Hi, I have the same type of question. I need to find out how many time a specific keyword can be found within a text.

                       

                      any clue?

                       

                      thanks

                      Antonio

                       

                      Created a calculated field:

                      SUM(IF CONTAINS(Text,"Smith") THEN 1 END)

                       

                      Replace Text with your field name and "Smith" with the text you're trying to find.

                      • 8. Re: Keyword search in a text string
                        Zac Hilbert

                        I was under the impression that you were trying to count the occurrences of substring with a string, but now it sounds like maybe you want to know how many rows in which the substring occurs.  If that's the case Tom W's calculated field should do just fine.

                         

                        If you want to take a more automated approach than searching with MS Word for your offline approach, I would recommend using some Linux flavored command line tools like grep and wc.  If you are using Windows, I recommend (GnuWin32). Cygwin in another possibility.

                        • 9. Re: Keyword search in a text string
                          Antonio Willybiro

                          Yes Tom's response is in the right direction but limits me to 1 occurrence per row.

                          I was indeed looking to have substring with a string so if on a row we have the keyword twice then the count should be 2 and not 1.

                           

                          Thanks for the tips.

                           

                          Best

                          Antonio

                          • 10. Re: Keyword search in a text string
                            Tom W

                            Sorry, my mistake.

                             

                            If you want the total times a word / phrase appears within a string, use a calculated field like follows (this is Psuedo code, not real Tableau code);

                             

                            (LEN(stringtosearch) - LEN(Replace(stringtosearch,"WordToFind",""))) / LEN("WordToFind")

                             

                            This will take the length of the string and subtract the length of the same string with the word you wish to find removed. You divide the result by the length of the word you wish to find and you have the count of the words.

                             

                            You'll need to wrap this within a SUM function to get a total across multiple rows.

                            • 11. Re: Keyword search in a text string
                              Antonio Willybiro

                              Great!

                              That's beautiful and works like a charm.

                              The formula I used based on the above attached sample workbook is:

                               

                              sum((LEN([The Text]) - LEN(Replace([The Text],[My Keyword],""))) / LEN([My Keyword]))sum((LEN([The Text]) - LEN(Replace([The Text],[My Keyword],""))) / LEN([My Keyword]))

                               

                              Thanks guys

                              Antonio