Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted

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

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
Highlighted

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

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

Highlighted

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

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

] ;


Highlighted

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

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;

Highlighted
Not applicable

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

Its work !

Thank you.