Counting Word Occurance in String Field 2

[MJRBIM]

CR-10 running against Oracle 9i.

I'm think that SQL or a SP is probably the best way to do this, but wanted to see if anyone had a slick way of doing it in CR-10.

There is a table with records and document titles in it, we would like to know which individual words are used most in the "Titles" field. The field is manual-text entry by the users - so there is no field standard for format, number of words, spelling, Case, etc.

(SAMPLE DATA)

RECORD TITLE
----------------------------
123 LETTER OF AGREEMENT
124 Final Agreement
125 Letter of Understanding
126 common sales agent agreement


Desired Results, sorted by ocurrence then alphabet...

WORD OCURRENCE
----------------------------
AGREEMENT 3
LETTER 2
OF 2
AGENT 1
COMMON 1
FINAL 1
SALES 1

If anyone has a formula (maybe a looping array?) that could tackle this without having to involve the DBAs it would be appreciated.

TIA for the help!

 

[synapsevampire]

You'll need to store EVERY word in an array (perhaps the uppercase of each), and simultaneously store the count in another, fairly complex stuff. As for spellings, that's a different story and you need to run it through some external process for that.

So we nab the words:

stringvar array Currentwords;
Stringvar array AllWords;
Numbervar array WordCount;
Numbervar Counter;
numbervar counter2;
Currentwords:=split({table.field}," ");
For Counter:= 1 to ubound(Currentwords) do(
if not(Currentwords[counter] in Allwords) then
redim preserve Allwords[ubound(Allwords)+1);
redim preserve Wordcount[ubound(Allwords)+1);
Allwords[ubound(allwords)]:=Currentwords[Counter];
Wordcount[ubound(allwords)]:=Wordcount[ubound(allwords)]+1
else
for counter2 := 1 to ubound[Allwords] do(
if Allwords[counter2] = Currentwords[counter] then
Wordcount[counter2]:=Wordcount[counter2]+1
);
);

Something like that, I can't test right now.

-k

 

[MJRBIM]

I'm not worried about the spellings for now...just the count would do.

In meetings all afternoon, but I'll try to review your formula suggestion later.

Thx.

 

[synapsevampire]

You can use the uppcase around each word to eliminate that aspect anyway, the theory is sound, I just don't have Crystal right now so you'll have to tweak it.

Good luck with it, MJRBIM.

-k

 

[craigK]

I'm having trouble working out the syntax errors. Can anyone help?

 

[MJRBIM]

I haven't had a chance to check it yet....

 

[ShortyA]

It may help if you provide more info on what error messages are appearing and where the cursor is pointing to when the error message appears...

ShortyA

 

[vidru]

See thread767-967452 for another solution.

-dave

 

[MJRBIM]

Dave -

Thanks for the linked solution, I have posted a follow-up on that thread.

Thanks!