Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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 |
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;
I think this makes no sense because QlikView stores only distinct values per field...