topic Re: Dynamic string search in App Development

Dynamic string search

nchamilton — Wed, 05 Jun 2024 12:53:31 GMT

Good morning,

I'm attempting to search a vendor table (vendor_name) and flag the vendor_name anytime the employee name shows up in the vendor name. I want to do this dynamically.

Example:

I would like to create a flag that flags the vendor_name anytime the emp_name is found in the vendor_name.

Employees:
Load * Inline [
emp_name
Jon Smith
Bob Thompson

];

Vendor:
Load * inline [
vendor_name
Jon Smith Inc
Bob Thompson LLC
Jones Co.

];

Expected Results:

vendor_name	employee_check	flag
Jon Smith Inc	Jon Smith	1
Bob Thompson LLC	Bob Thompson	1
Jones Co.		0
Jon Smith LLC	Jon Smith	1

Any assistance would be greatly appreciated.

Re: Dynamic string search

Or — Wed, 05 Jun 2024 13:15:09 GMT

Have a look at:

https://community.qlik.com/t5/New-to-Qlik-Analytics/wildmatch-data-from-two-tables/td-p/1563526

Re: Dynamic string search

p_verkooijen — Wed, 05 Jun 2024 13:21:35 GMT

You could concatenate the employeenames to a string and use this as a wildmatch.

WildMatch:

LOAD

CHR(39)&'*'&CONCAT(emp_name, '*'&CHR(39)&','&CHR(39)&'*')&'*'&CHR(39) AS WildMatchValues

RESIDENT Employees;

LET WildMatchValues=Peek('WildMatchValues',0,'WildMatch');

MatchVendor:

LOAD

IF(WILDMATCH(vendor_name, $(WildMatchValues)), 1, 0) AS flag

RESIDENT Vendor;

DROP TABLE Vendor;

SET WildMatchValues;

Re: Dynamic string search

nchamilton — Wed, 05 Jun 2024 13:59:16 GMT

These are both great. Thanks so much.