topic Re: Keyword Mapping in App Development

Keyword Mapping

ABP2021 — Mon, 20 Dec 2021 21:02:46 GMT

Hello Friends, Can any one tell me how to achieve this-
I have two tables Table 1 and Table 2 .

I wanted to group Field Text from Table 1 according to Field Keyword from Table 2 . below is the sample data and final output

Table 1		Table 2
Text		Keyword
Ashishkumar Thomar		Town
Ashish Home Town		Village
Darshan Rane Membership		Home
Pawan Membership Present		User
santosh Shah School		Membership
Nipul Pstel Homesider		Far
Hemant Town		Club
Khemani Hemant Far		Docs
Sandesh Village Township Number		School
Pusad City Far
User Module
Farm village
Deepak same town
Jitu naresh home
Homeron silk
Ashiwarya city club
Club house
The Door next Closed
Far away Docs
Accuser divice
User Text details

Re: Keyword Mapping

Roger2 — Wed, 17 Mar 2021 11:25:44 GMT

Hi, a simple solution to this problem will be to make use of the WildMatch function, see the script logic below and the result is attached:

//-----------------------------------------------------------------------------------------------------------//

// List of Keywords with the position they appear in the table
Table2:
Load *, RecNo() as Position;
Load * Inline [
Keyword
Town
Village
Home
User
Membership
Far
Club
Docs
School
];

// Create a variable to hold a concatenated list in the same orders as Table2
// Convert to Upper text to handle capitalisation irregularities
Table2_tmp:
Load Concat(Chr(39) & '*' & Upper(Keyword) & '*' & Chr(39),',',Position) as WordList Resident Table2;
Let vWordList = Peek('WordList');
Drop Table Table2_tmp;

// Load the Text and use the WildMatch function to find the position in the variable
// The position is a common field name that will join to Table2
Table1:
Load *,
WildMatch(Upper(Text),$(vWordList)) as Position;
Load * Inline [
Text
Ashishkumar Thomar
Ashish Home Town
Darshan Rane Membership
Pawan Membership Present
santosh Shah School
Nipul Pstel Homesider
Hemant Town
Khemani Hemant Far
Sandesh Village Township Number
Pusad City Far
User Module
Farm village
Deepak same town
Jitu naresh home
Homeron silk
Ashiwarya city club
Club house
The Door next Closed
Far away Docs
Accuser divice
User Text details
];

//-----------------------------------------------------------------------------------------------------------//

Please confirm if this answers your problem

Regards

Roger

Re: Keyword Mapping

ABP2021 — Wed, 17 Mar 2021 12:43:35 GMT

Thank you Rodger for your response.

There is one issue. as in your output Accuser divice is coming under User but it should not come under User it must check exact word. same in Homeron it should not come under Home.

Re: Keyword Mapping

Roger2 — Wed, 17 Mar 2021 16:10:50 GMT

The problem can be solved using the SubField function against the Text data as follows:

// List of Keywords
Table2_tmp:
Load * Inline [
Keyword
Town
Village
Home
User
Membership
Far
Club
Docs
School
];

// Load the Text (trim to remove trailing spaces) and the position number as a key field
Table1:
Load
RTrim(Text) as Text,
RecNo() as Position;
Load * Inline [
Text
Ashishkumar Thomar
Ashish Home Town
Darshan Rane Membership
Pawan Membership Present
santosh Shah School
Nipul Pstel Homesider
Hemant Town
Khemani Hemant Far
Sandesh Village Township Number
Pusad City Far
User Module
Farm village
Deepak same town
Jitu naresh home
Homeron silk
Ashiwarya city club
Club house
The Door next Closed
Far away Docs
Accuser divice
User Text details
];

// Cretae a temporary table containing all the words in the Text field
WordList_tmp:
NoConcatenate
Load
Position,
Capitalize(SubField(Text,' ')) as Word
Resident Table1;

// Load from the temporary table the keywords exist in Table2
Table2:
NoConcatenate
Load
Position,
Word as Keyword
Resident WordList_tmp
Where Exists(Keyword,Word);

Drop Table WordList_tmp,Table2_tmp;

Re: Keyword Mapping

Saravanan_Desingh — Wed, 17 Mar 2021 18:48:09 GMT

One solution using LevenshteinDist

Table1: Load RowNo() As ID,Capitalize(Text) As Text Inline [ Text Ashishkumar Thomar Ashish Home Town Darshan Rane Membership Pawan Membership Present santosh Shah School Nipul Pstel Homesider Hemant Town Khemani Hemant Far Sandesh Village Township Number Pusad City Far User Module Farm village Deepak same town Jitu naresh home Homeron silk Ashiwarya city club Club house The Door next Closed Far away Docs Accuser divice User Text details ]; Join Table2: Load * Inline [ Keyword Town Village Home User Membership Far Club Docs School ]; tabOut: LOAD RecNo() As RN,*, LevenshteinDist(Text, Keyword) As LevenDist, Len(Text) As L1, LevenshteinDist(Text, Keyword)*100/Len(Text) As Div Resident Table1; Left Join(tabOut) LOAD RN, If(Div<79,'Y') As LD_Flag Resident tabOut ; Drop Table Table1;

Re: Keyword Mapping

Saravanan_Desingh — Wed, 17 Mar 2021 18:48:41 GMT

Output:

Re: Keyword Mapping

ABP2021 — Thu, 25 Mar 2021 09:11:07 GMT

Thank you @Roger2 it works... 🙂