8 Replies Latest reply on Jan 15, 2019 1:59 PM by Chris McClellan

Automatically pull out common words from blocks of text?

michael.caloz Feb 9, 2015 4:02 PM

Hello!

I would appreciate your help to determine if this is possible in Tableau: I'm trying to design a worksheet to help identify my favorite flavors in wine. As you can see below, I have a spreadsheet with wine names, ratings, and tasting notes. From that, I want to figure out which specific tasting notes I like and don't like.

So, I would like for Tableau to look through all the tasting notes, pick out the common words, and find the average ratings for those wines. Thus, in the example above, it would identify that the word "oak" appears in both Wine A and Wine C; then, it would calculate that the rating for "oak" is 9.5 (average of 9 and 10). Similarly, it would identify that the word "pear" appears in both Wine A and Wine B, so it would calculate an average of 8.5.

If necessary, I could also manually go through the data and ensure that the tasting notes column is properly comma-separated (e.g., turn Wine A's tasting notes into "oak, pear"); but, it would be ideal if it could just automatically look up every word (e.g., find the averages for "hints," "of," "oak," etc.) and then I could just filter out the important ones later.

What do you think -- is this possible?

Thanks for any help you can provide,

Michael

1. Re: Automatically pull out common words from blocks of text?

Chris McClellan Feb 9, 2015 5:53 PM (in response to michael.caloz)
Sounds very possible, check the attachment.

156279.twbx 12.9 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Automatically pull out common words from blocks of text?

michael.caloz Feb 10, 2015 9:09 AM (in response to Chris McClellan)

Excellent -- thanks very much, Chris! This is exactly what I needed for a first step.

Now, do you have any suggestions for how to deal with a lot of keywords? I see that you set up the tasting note keywords by manually creating two calculated fields for each. But, my data set contains hundreds of wines whose tasting notes contain dozens or hundreds of potential keywords.

So, it seems like either: (1) I'll need to read through all the tasting notes and manually create a calculated field for each keyword therein, or (2) I'll need a way to have Tableau automatically create a calculated field for each word that appears in all the tasting notes.

Do you have any advice?
Like Show 0 Likes(0)

Actions
3. Re: Automatically pull out common words from blocks of text?

Joe Oppelt Feb 10, 2015 9:23 AM (in response to michael.caloz)

You want to reshape your data so that keywords are identified before tableau opens it.

Otherwise you'll need to create a million calcs, depending on how many words you want to examine. (OR, perhaps, have some secondary data source with the list of words you want to examine and do something with that.)

Consider googling "keyword search tool". There are all sorts of tools out there that will serve you better. Once you have it parsed, THEN you can use tableau to viz the results.
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Automatically pull out common words from blocks of text?

Praveena Prabhu Feb 10, 2015 10:37 AM (in response to michael.caloz)
Just an additional input, if you create a parameter as data type string Enter Text and use that in a calculated field-
if find([Notes], [Enter text]) > 0 then [Rating] end. That would make rating calculation dynamic. Attached is the workbook for reference.
Hope this helps.

text rating.twbx 9.5 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: Automatically pull out common words from blocks of text?

kettan Feb 10, 2015 10:59 AM (in response to michael.caloz)
You could use method 5 in The Cross Join Collection:

SELECT * FROM [data$] d, [lookup$] l WHERE ' ' + d.[Notes] + ' ' LIKE '% ' + l.[Word] + '[ ,;.:!?]%'

See more in attached files.

Ps. You need to open files with legacy connector if data source is Excel, Text, or Access.

thread 156279 Automatically pull out common words from blocks of text.twbx 12.2 KB

thread 156279 Automatically pull out common words from blocks of text.xlsx 5.8 KB
Like Show 1 Likes(1)

Actions
6. Re: Automatically pull out common words from blocks of text?

michael.caloz Feb 16, 2015 9:17 AM (in response to kettan)

Thanks very much, Joe, Praveena, and Kettan -- a combination of all of your answers is exactly what I needed! Thanks especially to Kettan for the idea to set up wordstems and the SQL query like that.

Have a great week,

Michael
Like Show 2 Likes(2)

Actions
7. Re: Automatically pull out common words from blocks of text?

Ismael Peregrina Jan 15, 2019 9:25 AM (in response to michael.caloz)

Hi Michael , did you get to finalize this tableau dashboard? I am looking to do exactly the same and it would be very useful if you could share any insight of how you finalized it.
Like Show 0 Likes(0)

Actions
8. Re: Automatically pull out common words from blocks of text?

Chris McClellan Jan 15, 2019 1:59 PM (in response to Ismael Peregrina)

The last time Michael logged in was Feb 2015, so it might be a better idea to start a new thread and the current users can help you
Like Show 0 Likes(0)

Actions