How to check if one column contain other column's text (substring) ?

[#1]

I have 2 tables :

Table1 :

load * inline [

num,text

1,record 1 is cool

2,record 2 is good

3, record 3 is awesome

] ;

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

Table2:

load * inline

[

term ,code

cool , a

awesome , b

good , c

]

I want to add another column into Table1 lets call it code. if text column of table1 contains any of record in  term column of table2, then  table1 code column = table2 code column else table1 code column = null ,  so the final result looks like the table below:

Table1 :

load * inline [

num,text , code

1,record 1 is cool , a

2,record 2 is good , c

3, record 3 is awesome , b

] ;

Thank you.

[sunny_talwar · Accepted solution]

May be something like this:

Table2:

Mapping

load * inline

[

term ,code

cool , a

awesome , b

good , c

];

Table1 :

LOAD *,

  Right(MapSubString('Table2', text), 1) as code;

load * inline [

num,text

1,record 1 is cool

2,record 2 is good

3, record 3 is awesome

] ;

[MayilVahanan]

Try like this

Table2:

Mapping

load * inline

[

term ,code

cool , a

awesome , b

good , c

];

Table1 :

LOAD *,

  ApplyMap('Table2', SubField(text, ' ', -1)) as code;

load * inline [

num,text

1,record 1 is cool

2,record 2 is good

3, record 3 is awesome

] ;

[sunny_talwar]

Another possibility:

Table1 :

load * inline [

num,text

1,record 1 is cool

2,record 2 is good

3, record 3 is awesome

] ;

Join (Table1)

load * inline

[

term ,code

cool , a

awesome , b

good , c

];

FinalTable:

LOAD num,

  text,

  If(WildMatch(text, '*'&term&'*'), code) as code

Resident Table1

Where Len(Trim(If(WildMatch(text, '*'&term&'*'), code))) > 0;

[#5]

Its work !

Thank you.