Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
mwscott1
Contributor

Match (Wild Match) to reduce data in load

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

   

TINEMAILNAME
123456654DAdams@ColonialLife.comADAMS, D
123456655JGivens@ColonialLife.comADAMS, D
123456656JAddy@ColonialLife.comADDY, J
123456657KMDanda@ColonialLife.comADDY, J
123456658BLMartyn@ColonialLife.comAMOS, K
123456659KAmos@ColonialLife.comAMOS, K
2 Replies
vishsaggi
Esteemed Contributor III

Re: Match (Wild Match) to reduce data in load

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;


robin_hausdoerfer
Valued Contributor III

Re: Match (Wild Match) to reduce data in load

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