Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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
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
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