Keyword Mapping

[ABP2021]

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

[Roger2]

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

[ABP2021]

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.

[Roger2 · Accepted solution]

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;

[Saravanan_Desingh]

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; 

[Saravanan_Desingh]

Output:

[ABP2021]

Thank you @Roger2  it works... 🙂