topic Re: Finding the highest word count. To identify trends. in QlikView

Finding the highest word count. To identify trends.

bnelson111 — Fri, 27 Jun 2014 21:44:19 GMT

As I'm sure most people will have a problem with unstructed text fields, for example some people may write two sentences in a comment field while others 3 words or one. I'm looking a way to interrogate a field which may contain 1 word to 5 sentences or more to try and find the most commonly used words, so that we can attempt to discover failure trends.

For example.

Operator Comments	Machine	Job Number
Station 3 problems	Press 1	12345
Failure on Station issues	Press 2	23233
Spill on Station	Press 1	3232323
Working hard all day and Station issues.

I want to display counts:-

Station = 4

issues = 2

on = 2

All others count1?

There are 1000's of comments so may be best not to have any fix in the script. Also the keys words will change based on date ranges. Any ideas? Thanks.

Re: Finding the highest word count. To identify trends.

maxgro — Fri, 27 Jun 2014 22:43:06 GMT

maybe some help in a similar discussion here

word clouds

Re: Finding the highest word count. To identify trends.

bnelson111 — Sat, 28 Jun 2014 14:31:49 GMT

Was hoping to have just a count within qlikview if at all possible.

Re: Finding the highest word count. To identify trends.

swuehl — Sat, 28 Jun 2014 14:41:17 GMT

Well, John's example in that referenced thread does the word count within QV only, right?

Re: Finding the highest word count. To identify trends.

MarcoWedel — Sat, 28 Jun 2014 22:12:38 GMT

Hi,

one solution at script level:

LOAD SubField(PurgeChar([Operator Comments],'.'), ' ') as Word
FROM [http://community.qlik.com/thread/123629]
(html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Finding the highest word count. To identify trends.

bnelson111 — Sun, 29 Jun 2014 11:16:23 GMT

Brilliant!!! Thanks