Summarising text patterns in a compound string

[Ted Giles]

My next challenge is to read through half a million records and analyse the text in one field.
The field has numerics - which I am not interested in - and text which I need to look into.
Text can be similar to;
Genogram,
CDF Referral
Third Party Document regarding housing allowance.

They are actually file names.


Is there any way to summarise contiguous text blocks containing different text items?
Contiguous text is what I am after and there will be a shed load, I realised that.
I can chop and slice once I get the main elements.

[Stan Mathews]

Re: Summarising text patterns in a compound string

Actual sample field contents?

fieldname = "2345347Genogram 9473573 "
? rtrim(ltrim(fieldname,"0123456789"),"0123456789")
= "Genogram"

[Ted Giles]

Re: Summarising text patterns in a compound string

Problem is Stan, I don't have a handle on the text block.
How would I put this in a Summarise op?

[Stan Mathews]

Re: Summarising text patterns in a compound string

Actual sample field contents? I don't have a handle on what you mean.

Is

Genogram,
CDF Referral
Third Party Document regarding housing allowance.

in one field? Is this a memo field?

[Ted Giles]

Re: Summarising text patterns in a compound string

No Stan, each is in a single field.
There are 500k records with a structure of
Id/name/text.
The examples are in separate records.
It's not a Memo field

What I am attempting to do is to identify the different words used in the text field.
The users were given instructions to use certain phrases but they are pretty mixed up.

A summary of different words would get me started, like

Genogram 155
CDF 200
Referral 566
Third 122
Party 124

[GGandhi]

Re: Summarising text patterns in a compound string

Problem is Stan, I don't have a handle on the text block.
How would I put this in a Summarise op?

if you write a function as follows that will give any alpha string in string containing varied numbers in front or in the back of the string to harvest.

FUNCTION stripN AS C (inputStr AS C )
	
	while isalpha(inputStr)=.f.
		inputStr=right(inputStr,len(inputStr)-1)
	end while
	while isalpha(right(inputStr,1))=.f.
		inputStr=left(inputStr,len(inputStr)-1)
		end while
	stripN = inputStr
END FUNCTION

?txt
= "1093103ksdsdj13031"

?stripN(txt)
= "ksdsdj"

once it is done would you be able to run the summarize operation?

[Ted Giles]

Re: Summarising text patterns in a compound string

I will try this G.
If I can use the logic in a Summarise Condition that would be great.
Not sure I can though.

The other problem is differentiating between 1234Text Different text3456
I'm trying to isolate contiguous text so the above would come out as

Text 2
Different 1

[GGandhi]

Re: Summarising text patterns in a compound string

are these
1234Text
Different
text3456
like in each record
or one record will have
1234Text Different text3456
?

[Stan Mathews]

Re: Summarising text patterns in a compound string

A summary operation won't do you any good until you have the data in proper form.

You need to create a destination table and then a script that writes the text values to the destination

open the original table
fetch the first record
open the destination table
begin a while loop
extract the text in the first record, rtrim(ltrim(fieldname,"0123456789"),"0123456789") and stripn() do the same thing
get the unique words in the text with WORD_UNIQUE()
count the unique words with w_count()
begin a for....next loop using that count
enter records in the destination table for each of the unique words
end the for....next loop
fetch the next record in the original table
end the while loop
close both tables

Now you can summarize the destination table to get what you want.

[Ted Giles]

Re: Summarising text patterns in a compound string

Thanks for the good advice and options both of you.
What I ended up doing was a mixture.
I ran an Update procedure to get rid of 0-9, &,%,-, and any spurious characters.
Then I created a table with 5 text fields in it and used Word() to pick the first 5 words out and load them into the table.
I'd forgotten about Unique, so that is the next part of the operation.
As I said, many thanks and just talking about it helped a lot.
Ted

[Stan Mathews]

Re: Summarising text patterns in a compound string

If you want the script.

o_tbl = table.open("original",FILE_RW_EXCLUSIVE)
tbl.fetch_first()
d_tbl = table.open("destination",FILE_RW_EXCLUSIVE)
while .not. o_tbl.fetch_eof()
	wds = rtrim(ltrim(textfieldname,"0123456789"),"0123456789")
	uwds = word_unique(wds," ")
	cnt = w_count(uwds)
	for qx = 1 to cnt
		d_tbl.enter_begin()
		d_tbl.wordfieldname = word(uwds,qx," ")
		d_tbl.enter_end()
	next qx
	o_tbl.fetch_next()
end while
o_tbl.close()
d_tbl.close()

[Ted Giles]

Re: Summarising text patterns in a compound string

That is very kind of you Stan.
My analysis so far has uncovered a load of worms.
People have been transposing text.
One will put a descripter in as "Assessment Core Meeting"
Another as "Core Assessment Meeting
They are the same thing.

So I will still need to eyeball the results and come up with a taxonomy which works for the client.

[Stan Mathews]

Re: Summarising text patterns in a compound string

You will be able to streamline the process somewhat if you filter for

"core" $ fieldname .and. "assessment" $ fieldname

for example. This will allow you to see common terms, possibly use copy-paste or an update operation once you are sure the records are truly grouped as you want..

Thinking you could update the table with "Core Assessment Meeting" filtering for

"core" $ fieldname .and. "assessment" $ fieldname .and. "meeting" $ fieldname

?

[MoGrace]

Re: Summarising text patterns in a compound string

Taxonomy? oooohh good one Ted!

[Ted Giles]

Re: Summarising text patterns in a compound string

You will be able to streamline the process somewhat if you filter for

"core" $ fieldname .and. "assessment" $ fieldname

for example. This will allow you to see common terms, possibly use copy-paste or an update operation once you are sure the records are truly grouped as you want..

Thinking you could update the table with "Core Assessment Meeting" filtering for

"core" $ fieldname .and. "assessment" $ fieldname .and. "meeting" $ fieldname

?

Thanks Stan. The object of the exercise is to identify the Key Words so I will look into this.