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

[mrichman]

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.

[lironbaram]

Hi

You can try this

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

[mrichman]

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]

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]

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]

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

-Rob

[rwunderlich]

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]

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]

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]

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