Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

1 Solution

Accepted Solutions
sunny_talwar

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

] ;


Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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

] ;


Capture.PNG

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

] ;


Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
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;

Not applicable
Author

Its work !

Thank you.