topic Re: Lookup or applymap or by using variable list, search in QlikView

Lookup or applymap or by using variable list, search

vinaymaidargi — Sun, 13 Aug 2017 17:50:12 GMT

Hello All,

I have a scenario, where in i have a list of keywords for different category, which i need to search or look upon in the 7 columns in the same table.

For example:

Keyword list: in excel file

Keywords: School, Jurisdiction etc... some 70 words

Category: Govt

Keywords: Gifts, Entertainment, Movie etc.. some 30 words

Category: Gifts/Entertainment

Table Moonshine:

We have fields like Report Name, Attendee Company etc likewise 8 fields in Moonshine table which needs to be looked. So, if any of this fields contains the keywords, it should flag. Some of the keywords are wild search and some of them exact search.

The keyword list can be added at a later stage as well, so we should give the user the capability to add the keywords.

How do I implement the same?

Thanks,

Vinay

Re: Lookup or applymap or by using variable list, search

Anil_Babu_Samineni — Mon, 14 Aug 2017 07:56:55 GMT

I think your explanation is some how reasonable. But, Better if you provide data set and result set

Re: Lookup or applymap or by using variable list, search

vinaymaidargi — Mon, 14 Aug 2017 08:34:30 GMT

Hi Anil, Attached is the screenshot.

Left side is the keyword list. Those keywords need to be looked upon the fields marked red. If those keywords are found in the fields, it should flag.

For example: Report Name field may contain value like 'MCC International School'. So the keyword list has the word school. So it should give a flag

Re: Lookup or applymap or by using variable list, search

Anil_Babu_Samineni — Mon, 14 Aug 2017 08:40:00 GMT

Does it possible to share Sample to look?

Perhaps this?

If(SubStringCount([Report Name], 'MCC International School'), 'Y', 'N') as NewField

Re: Lookup or applymap or by using variable list, search

akshaye_c_navale — Mon, 14 Aug 2017 08:51:16 GMT

Can you share more details regarding requirement?

Thanks,

Akshay

Re: Lookup or applymap or by using variable list, search

akshaye_c_navale — Mon, 14 Aug 2017 09:27:12 GMT

Hi Vinay,

First you need to create condition for Exact match and Wild Match after loading data using Match and WildMatch.

and that variables you need used in condition based on type of search.

below is sample code.

if any doubts let me know.

ExactMatchText_Table:
Load distinct chr(39) & Concat([Keyword],chr(39) & ',' & chr(39)) & chr(39) as ExactMatchText
resident DataSource;

WildMatchText_Table:
Load distinct chr(39)& '*' & Concat([Keyword],('*' & chr(39) & ',' & chr(39) & '*') )& '*' & chr(39) as WildMatchText
resident DataSource;
Let ExactMatchCondition =Peek('ExactMatchText',0,'ExactMatchText_Table');
Set ExactMatchCondition='If(Match([Field Name],$(ExactMatchCondition)),''Y'',''N'')';

Let WildMatchCondition =Peek('WildMatchText',0,'WildMatchText_Table');
Set WildMatchCondition='If(WildMatch([Field Name],$(WildMatchCondition)),''Y'',''N'')';

Final Table:
Load Keyword,If([Type of Search]='Wild',$(WildMatchCondition),$(ExactMatchCondition)) As Condition1

,If([Type of Search]='Wild',$(WildMatchCondition),$(ExactMatchCondition)) As Condition2

Resident DataSource;

Thanks,

Akshay

Re: Lookup or applymap or by using variable list, search

vinaymaidargi — Thu, 17 Aug 2017 17:02:50 GMT

Thanks Akshay,

i have implemented this, but however, below is further clarifications.

Also, the keyword list and the fields which are looked upon are two separate tables.

Exact match

Some keywords are defined as exact match, e.g. nih. the search will be on ‘ nih ‘; with spaces around nih. Any form of nih will be ok, like NIH, Nih, etc but not nihil.

In case we find a hit on school related to Report Name and on the same row we find another hit on Business Purpose (report) we have 2 hits for the same Event

In case we find a hit on school related to Report Name and on the same row we find teacher related to Report Name, we have 2 hits for the same Event

In case we find a hit on school related to Report Name and on the same row we find teacher related to Business Purpose (report) we have 2 hits for the same Event.

So it needs to go row by row. So whether we can use a for loop for going row by row.

Please let me know if I am not clear.