topic Re: Extract words from a free text field containg a description and adding those words one by one to another table in App Development

Extract words from a free text field containing a description and adding those words one by one to another table

DoctorPolidori — Tue, 20 Feb 2024 16:27:33 GMT

I need some ideas to build a LOAD script to extract words from a free text field containing a description and adding those words one by one to another table

A pseudo code could look like this:

for each description_field in table_A
for each description_word in description_field
insert word in table_B
next description_word
next description_field

Re: Extract words from a free text field containg a description and adding those words one by one to another table

Oleg_Troyansky — Tue, 20 Feb 2024 16:21:51 GMT

Hi,

You can use the function SubField, with the space as a delimiter - that will break the text into individual words. Then, you should probably worry about punctuation characters - commas, periods, etc, and remove them using the function PurgeChar().

Cheers,

Re: Extract words from a free text field containing a description and adding those words one by one to another table

marksouzacosta — Tue, 20 Feb 2024 16:27:35 GMT

Hi @DoctorPolidori ,

SubField is the function you are looking for.

Here is an example:

// Source Sample data
Source:
LOAD * INLINE [
RecordNo|Description
1|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
](delimiter is '|');

// Description will be splited by space
WordsWithCommmaTable:
LOAD
  RecordNo,
  Subfield(Description,' ') AS WordWithComma
RESIDENT
 Source
;

As @Oleg_Troyansky suggested you can do further clean-up using PurgeChar or other funcionts to remove unwanted characteres.

This is how the table WordsWithCommaTable looks like: