Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrichman
Creator II
Creator II

Using WildMatch and another field as the expression (Different Tables)

Hi Community,

How would you do this if field1 and field2 are from separate tables and there is no possibility to join them?

Referring to the following thread Re: Using WildMatch and another field as the expression

Sunny's answer: If(WildMatch(Field1,'*' & Field2 & '*'), 'Match', 'No Match')

Thanks in advance.

9 Replies
lironbaram
Partner - Master III
Partner - Master III

Hi

You can try this

If(WildMatch(Field1,concat(distinct Field2 , '*,*'), 'Match', 'No Match')


mrichman
Creator II
Creator II
Author

Hi Liron,

Thanks for your assistance. Unfortunately, it's not working. Here's an example, hopefully this would help.

Data1:

Load* Inline [

Keyword,

Mark,

Benjamin,

Jose,

];

Data2:

Load* Inline [

Description,

Mark Simon,

Gabby Caprio,

Nico Jose,

];

I'm trying to create a straight table where If(WildMatch(Description,'*' &Keyword& '*'), 'Match', 'No Match').


Thanks in advance.

petter
Partner - Champion III
Partner - Champion III

In a load script you can create a string out of all the values from the Field2. This string can be put into a variable and then you can use $-sign expansion to do a WildMatch. However it would be a limitation on the number of values that the Field2 could have for this to work at all. I guess it might work as long as the Field2 number of values is not more than a few hundred - mostly speedwise. This has to be tested and there is no documentation of these limitations.

vFieldSeparator='*'&Chr(39)&','&Chr(39)&'*';

FIELD2_VALUES:

LOAD

  Chr(39)&'*'&Concat(DISTINCT Field2,$(vFieldSeparator),Field2)&'*'&Chr(39) AS F2values

RESIDENT

  Table_of_Field2;

vF2v=Peek('F2values');

DROP TABLE FIELD2_VALUES;

....

      If(WildMatch(Field1,$(vF2v),'Match','No Match')

.....

v2F2v=;

I have not tested the code but it should be pretty close to working or working as is...

mrichman
Creator II
Creator II
Author

Hi Petter,

Thanks for your assistance, the script is not working. I'm getting the following error:

Unexpected token: ')', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...

Data example:

Data1:

Load* Inline [

Keyword,

Mark,

Benjamin,

Jose,

];

Data2:

Load* Inline [

Description,

Mark Simon,

Gabby Caprio,

Nico Jose,

];

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Could you explain your use case?  Perhaps WildMatch() is not the best approach.

-Rob

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Here's a working example of something similar to what Petter is proposing.

Qlikview Cookbook: Mapping With Wildcards http://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

Note that if you are a Qlikview Components user, you can create the wildmatch expression with:

CALL Qvc.CreateWildMapExpression (vMapExpr, WildMapTable);

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

mrichman
Creator II
Creator II
Author

Hi Rob,

Thanks for your message. Yes here's a data sample. Data1 table consists of keywords and Data2 consists of a description. I'm would like to search the keywords in the description and if exists then return 1 and if not then a 0 for example.

Thanks in advance.

Data example:

Data1:

Load* Inline [

Keyword,

Mark,

Benjamin,

Jose,

];

Data2:

Load* Inline [

Description,

Mark Simon,

Gabby Caprio,

Nico Jose,

];

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Take a look at this example that does just that, indexes keywords.  The example is written for QlikView, you can download QLikView Personal edition to open it.  I'll get around to creating a Qlik Sense version if you're interested.

Qlikview Cookbook: Indexing Keywords in Text http://qlikviewcookbook.com/recipes/download-info/indexing-keywords-in-text/

-Rob

t_nikhilareddy
Contributor II
Contributor II

Did someone find an answer to this? I'm having the same problem.