Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrichman
New Contributor III

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.

8 Replies
lironbaram
Honored Contributor II

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

Hi

You can try this

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


mrichman
New Contributor III

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

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.

MVP
MVP

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

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
New Contributor III

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

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,

];

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

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

-Rob

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

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
New Contributor III

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

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,

];

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

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