3 Replies Latest reply: Aug 14, 2013 7:42 AM by Christophe Brock RSS

    Left Join based on pattern

    Christophe Brock

      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

        • Re: Left Join based on pattern

          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
            Tresesco B

            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
              Christophe Brock

              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