Match (Wild Match) to reduce data in load

[mwscott1]

I have two tables "table1" contains the NAME, and TIN. "Table2" contains the TIN, and EMAIL. In some cases users from "table2" share the same information with the exception of the email address. I use the TIN to join the two tables. This leaves me with two rows of data per NAME. I need to reduce the data further by matching the EMAIL with the NAME, so that I get one row. The format of the EMAIL and NAME are always the same (Results below). Is there a way to match the EMAIL and the NAME? I only need to return the ones that match (examples in red)

CODE

Table1:

SQL SELECT

“NAME",
“TIN”
FROM ABDZ.MAGC;

LEFT JOIN

Table2:

SQL SELECT

“TIN”,
"EMAIL"

FROM ZDBA.CSEA;

RESULTS DATA

TIN	EMAIL	NAME
123456654	DAdams@ColonialLife.com	ADAMS, D
123456655	JGivens@ColonialLife.com	ADAMS, D
123456656	JAddy@ColonialLife.com	ADDY, J
123456657	KMDanda@ColonialLife.com	ADDY, J
123456658	BLMartyn@ColonialLife.com	AMOS, K
123456659	KAmos@ColonialLife.com	AMOS, K

[vishsaggi]

Try this may be:

LOAD *

WHERE Names = EmailName;

LOAD *, Lower(Mid(Subfield(EMAIL, '@',1), 2)) AS EmailName,

        Lower(Subfield(NAME, ',',1))  AS Names INLINE [

TIN, EMAIL, NAME

123456654, DAdams@ColonialLife.com, 'ADAMS, D'

123456655, JGivens@ColonialLife.com, 'ADAMS, D'

123456656, JAddy@ColonialLife.com,    'ADDY, J'

123456657, KMDanda@ColonialLife.com, 'ADDY, J'

123456658, BLMartyn@ColonialLife.com, 'AMOS, K'

123456659, KAmos@ColonialLife.com, 'AMOS, K'

]

;

So technically based on your script.

Table1:

SQL SELECT

“NAME",
“TIN”
FROM ABDZ.MAGC;

LEFT JOIN

Table2:

SQL SELECT

“TIN”,
"EMAIL"

FROM ZDBA.CSEA;

NOCONCATENATE

FINAL:

LOAD *

WHERE Names = EmailName;

LOAD *,

        Lower(Mid(Subfield(EMAIL, '@',1), 2)) AS EmailName,

        Lower(Subfield(NAME, ',',1))  AS Names

RESIDENT Table1:

Drop Table Table1;

[Anonymous]

I think this makes no sense because QlikView stores only distinct values per field...