-
1. Re: Keyword search in a text string
Russell Christopher Oct 23, 2012 1:57 PM (in response to Christopher Garrabrant, Ph.D.)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. Oct 23, 2012 2:00 PM (in response to Russell Christopher)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 Oct 23, 2012 2:13 PM (in response to Christopher Garrabrant, Ph.D.)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!
-
Book11.twbx.zip 54.9 KB
-
-
4. Re: Keyword search in a text string
Antonio Willybiro May 5, 2014 1:20 PM (in response to Russell Christopher)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 May 5, 2014 2:37 PM (in response to Antonio Willybiro)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 May 5, 2014 2:41 PM (in response to Zac Hilbert)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 WMay 5, 2014 2:59 PM (in response to Antonio Willybiro)
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 May 6, 2014 7:29 AM (in response to Antonio Willybiro)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 May 6, 2014 7:37 AM (in response to Zac Hilbert)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 WMay 6, 2014 7:39 AM (in response to Zac Hilbert)
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 May 6, 2014 7:49 AM (in response to Tom W)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
-
12. Re: Keyword search in a text string
cesar gomez Oct 18, 2018 7:20 AM (in response to Russell Christopher)Russell :
This very helpful but I want to make it more complicated. So I have an excel file with a column with text. I want to be able to search that column based on certain parameters and then label it into a specific "bucket" label. Below an example of the terminology I am looking to filter but don't know how the terminology will work on a If and else statement.
Here is how I want to filter out to specific Label by word searching an excel column:
("eng" or "eng1" or "eng2" or "eng3" or "eng4" or "engine" or "engines" ) and ("shut down" or "shutdown" or "IFSD") and ("atb" or NEAR(("emerg*", "declar*"), 2, FALSE) or "diver*" or "turn back" or "turnback" or "turned back" or "mayday" or "may day" or "21.3") and not (NEAR(("no", "shut*"), 2, TRUE) or NEAR(("not", "shut*"), 2, TRUE) or NEAR(("apu", "shut*"), 2, FALSE) or NEAR(("no", "emerg*"), 2, TRUE))
I Thank you ahead of time.
Cesar A.
-
13. Re: Keyword search in a text string
cesar gomez Oct 22, 2018 11:37 AM (in response to cesar gomez)So I got Half way there but I still need help below what I got so far:
IF (CONTAINS([Event Description],'eng') OR CONTAINS([Event Description],'eng1')OR CONTAINS([Event Description],'eng2')OR CONTAINS([Event Description],'eng3')OR CONTAINS([Event Description],'eng4')OR CONTAINS([Event Description],'engine'))
AND CONTAINS([Event Description],'shutdown' )
THEN 'Engine Shutdown'
ELSE 'TRY AGAIN'
END
My problem is that I need to add more 'keywords' to the AND part of the formula as OR type. For Example : AND CONTAINS ([Event Description], 'Shutdown' or 'Shut down' or 'Emer' or......)
HELP