Return columns that match with wildcard - Qlik Community

mwscott1 · ‎2016-12-30

I have two table Table 1 and Table 2 that are LEFT joined by TIN. However, multiple users share the same TIN and NAME. The only difference in the two rows would be the ELEC_ADDR_VAL. I would like to further reduce this using columns NAME and ELEC_ADDR_VAL. to only return rows with a match.

SQL SELECT

"NAME",

TIN

FROM TABLE 1;

LEFT JOIN

SQL SELECT

"CBO_VAL" as TIN,

"ELEC_ADDR_VAL"

FROM TABLE 2;

TIN	NAME	ELEC_ADDR_VAL
999123456	Smith, John	JSmith@Email.com
999123456	Smith, John	SQue@Email.com

The last name is always present in both columns.

Miguel_Angel_Baeyens · ‎2016-12-30

From memory, something like this should work See attached app:

SQL SELECT

"NAME",

TIN

FROM TABLE 1;

LEFT JOIN

LOAD *

WHERE WildMatch(SubField(ELEC_ADDR_VAL, '@', 1), '*' & SubField(CBO_VAL, ',', 1) & '*') > 0;

SQL SELECT

"CBO_VAL" as TIN,

"ELEC_ADDR_VAL"

FROM TABLE 2;

EDIT: The WHERE clause modified to "> 0"

mwscott1 · ‎2016-12-30

This does not seem to we working in my script. I am loading two separate tables that are joined by the TIN column. Where do I put the WHERE statement you gave me. When I try it like you have it above I get an error "Field not found - <NAME>".