Match and lookup

[Anonymous]

Hi need to search for a list of word in a column and if present , with that lookup value .

In excel i have two work book , wk1 and Wk2 , in WK1 there is column " description" contains about 4 line of ticket description and in WK2 i have list of word which need to be searched in "Description" , If the value is there for a value, then need to fill the search word

the formula i use in excel in "INDEX(Sheet1!$B:$B,MATCH(TRUE,ISNUMBER(SEARCH(Sheet1!$B:$B,Sheet2!J30)),0))"

where - Sheet1!$B:$B - List of words to be searched

Sheet2!J30 - Descrition colmun - where i do a search

can any one help in Qlik sense script

[Quy_Nguyen]

Hi,

Maybe you should use SubField to break your Description into words, after that you can associate it with your Key search.

Attached is an example that i have just created. Please have a look at it.

[Quy_Nguyen]

Sorry, the above solution is not good incase your Search key contains more than 1 word.

Let see another approach:

Join your description with SearchKey, filter the result table with Index function condition. Then associate with Search Key.

Detail in attachment.

[OmarBenSalem]

I invite you to create this script and test it, to understand it and adapt it t your needs:

Keywords:

LOAD * INLINE [

Keyword

Clean

Pool

Service

Kids

Vacation

]

;

Reviews:

LOAD *, RecNo() as ReviewId INLINE [

Review

The pool was great

Great place for kids. Very clean.

The staff was terrible. Bad stay all around.

Very clean pool. Great service!

]

;

Inner Join(Keywords)

LOAD

          ReviewId,

          subfield(Capitalize(purgechar(Review, '.!,')), ' ') as Keyword

RESIDENT Reviews

;

and please refer to this thread:

Find Key Words and Phrases in Free Text Fields

[Anonymous]

Hi

I have keyword of around 500 key word , howto do it then

[OmarBenSalem]

Are your keywords a oclumn in an excel table?

[Anonymous]

Yes

There are two tables, Table1 contains description and table2 contains keyword in a column

[OmarBenSalem]

same approach then :

Keywords:

load Keyword

from source1;

Description:

LOAD *, RecNo() as DescriptionId ;

load Description

from source2

;

Inner Join(Keywords)

LOAD

          DescriptionId,

          subfield(Capitalize(purgechar(Description, '.!,')), ' ') as Keyword

RESIDENT Description

;