topic Left Join based on pattern in QlikView

Left Join based on pattern

Anonymous — Wed, 14 Aug 2013 08:19:40 GMT

Hello,

I'm having the following scenario:

In table A, I have a couple of email addresses:

Table A:

EMAILAddresses

In table B, I have a list of patterns:

Table B:

Patterns

yahoo.com

details

I want to put a flag in a new column in Table A where the email addresses matches one of the patterns from Table B.

So the output would need to become:

Table A:

EMAIL	FLAG
a@gmail.com
b@gmail.com
c@yahoo.com	1
d@yahoo.com	1
e@details.be	1

Is there a way to flag the rows in table A based on the patterns in table B?

In my example, there are only a few patterns. In reality, there will be hundreds.

Any help would be greatly appreciated.

Best regards,

Christophe

Re: Left Join based on pattern

Wed, 14 Aug 2013 08:27:18 GMT

You could try this:

Create a new field in table A, that contains all the distinct values in the patterns field, but surrounding them with the '*' character (*pattern*), and concatenate them with the '|' character:

'*details*'|'*gmail.com*'...

Then, use the wildmatch function in every record of the table to flag the coincidences

Re: Left Join based on pattern

tresB — Wed, 14 Aug 2013 08:34:09 GMT

try like this:

TableA:

Load

SubField(EMAILaddresses, '@' , 2) as Key,

EMAILaddresses

From <>;

RIGHT Join

Load

SubField(EMAILfield, '@' ,2) as Key,

From <second table>;

FinalTable:

Load

If( NOT isnull(EMAILaddresses),1) as Flag

Resident TableA;

Drop table TableA;

Re: Left Join based on pattern

Anonymous — Wed, 14 Aug 2013 11:42:17 GMT

Hi,

Thanks for all your very fast feedback ! (amazing !!)

Using some of your input, I eventually came to this, which worked out fine for me:

// Exclude email addresses matching a certain pattern

TableB:

LOAD

Chr(39) & Concat('*' & [Email Pattern] & '*',Chr(39) & ',' & Chr(39)) & Chr(39) AS Pattern

FROM

[ExcludePatterns.xlsx]

(ooxml, embedded labels, table is Sheet1);

let vPatterns = Peek('Pattern',0,'TableB');

QUALIFY *;

TableA:

LOAD *,

wildmatch(EMAIL,$(vPatterns)) as TestUserFlag

Resident TableX;

In the TableA, the only thing I need to filter on then is anything > 0 to find out which are the addresses to be filtered out... !

Thanks again for all your support,

Christophe