Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
christophebrock
Creator
Creator

Left Join based on pattern

Hello,

I'm having the following scenario:

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

Table A:

EMAILAddresses

a@gmail.com

b@gmail.com

c@yahoo.com

d@yahoo.com

e@details.be

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:

EMAILFLAG
a@gmail.com
b@gmail.com
c@yahoo.com1
d@yahoo.com1
e@details.be1

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

1 Solution

Accepted Solutions
christophebrock
Creator
Creator
Author

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

View solution in original post

3 Replies
Not applicable

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

tresesco
MVP
MVP

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;

christophebrock
Creator
Creator
Author

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

View solution in original post