topic Reocurring words (trend words) in QlikView

Reocurring words (trend words)

Wed, 18 Aug 2010 18:58:55 GMT

Hi,

I would like to put in a Report the most used words in a Field, as we see in the trend words on Tweeter or even the tags box in the Qlik Community (below)

Does any body knows how it could be done?

If there is a way to show the index Qlikview creates and it uses with the search box?

Thanks a lot.

Reocurring words (trend words)

johnw — Wed, 18 Aug 2010 19:26:37 GMT

Use subfield() to break your text into words, and then just count. See attached example.

As for what QlikTech is doing for their tag list, people are tagging their posts, so each tag is a separate record, and you're not counting words in text. Very different.

Reocurring words (trend words)

Wed, 18 Aug 2010 19:52:05 GMT

Hi John,

thanks for the quick response. That was fast! I think that will help.

Just to make sure:

- mapsubstring('TextMap', subfield(capitalize(keepchar(upper(Text),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ')),' ') ) as Word

This will search the words, separated by ' ', keeping only the letters. Why the mapsubstring? Substitute Qlikview for Qlikview?

- INNER JOIN (Words)
LOAD ID, Word
RESIDENT Words
WHERE len(trim(Word))
;

You are loading the table again, getting rid of empty strings and spaces, correct?

Thanks again.

Reocurring words (trend words)

johnw — Wed, 18 Aug 2010 20:03:27 GMT

Yep, you understood it all correctly. There's probably a way to get rid of empty strings and spaces without the inner join, which would be better, but I got lazy and just did what I knew would work since it was just an example. It wasn't very important to convert "Qlikview" to "QlikView", but I thought it would be good to have the text map example there, since there could be a lot of words that the functions aren't going to capitalize correctly.