3 Replies Latest reply: Apr 30, 2012 8:10 AM by mrpjspencer RSS

    Filtering out invalid email addresses

      Morning, I cant get the script below to produce the result I want, on my system at work you can't have a blank email address field for customer so we put N@ or n@. So I wrote a script below to exclude these for an audit.

       

      Table1:

      LOAD Standard_Sender_Address,

      Email_Address

      FROM

      [C:\Qlik View\Reports\email.qvd]

      (qvd)

      where Standard_Sender_Address='X'

      or (left(Email_Address,2)<>'N@')

      or (left(Email_Address,2)<>'n@')

      ;

       

      This is still giving me the the emails I dont want.

       

      i'm a bit baffled as this looks like the correct way to do it.

       

      Paul.

        • Filtering out invalid email addresses
          Miguel Angel Baeyens de Arce

          Hi,

           

          Do you mean you don't want to load all email addresses from the QVD file that start with "N@"? If so, the following should work:

           

          WHERE Upper(Left(Email_Address, 2)) <> 'N@';

           

          Hope that helps.

           

          Miguel

          • Filtering out invalid email addresses
            Celambarasan Adhimulam

            Hi,

                 In your way it should be

                

            Table1:

            LOAD Standard_Sender_Address,

            Email_Address

            FROM

            [C:\Qlik View\Reports\email.qvd]

            (qvd)

            where Standard_Sender_Address='X'

            or (left(Email_Address,2)<>'N@'

            and left(Email_Address,2)<>'n@')

            ;

            Its better to use upper or Lower functions to make case insensitive comparison

             

            where Standard_Sender_Address='X'

            or Upper(left(Email_Address,2))<>'N@'

            ;

            Or

             

            where Standard_Sender_Address='X'

            or Lower(left(Email_Address,2))<>'N@'

            ;

            or use wildmatch

            where Standard_Sender_Address='X'

            or not WildMatch(Email_Address,'N@*')

            ;

             

            or use MixMatch

             

            where Standard_Sender_Address='X'

            or MixMatch(left(Email_Address,2)),'N@')

            ;

            I suggest you to use wildMatch.

             

            Celambarasan

            • Filtering out invalid email addresses

              I fiinally figured it out! The script below will help filter out unwanted email addresses where people have filled in a free format mix of n/a,n@a or ask (change it relevent to the data on your specific system)

               

               

              Table1:

              LOAD Standard_Sender_Address,

              Email_Address

              FROM

              [C:\Qlik View\Reports\email.qvd]

              (qvd)

              where Standard_Sender_Address='X'

              and not WildMatch(Email_Address,'N@*')

              and not WildMatch(Email_Address,'N'&chr(39)&'@*') This filters out email addresses with n’@* in it

              and not WildMatch(Email_Address,'ask*')

              and not WildMatch(Email_Address,'n/a*')

              and not WildMatch(Email_Address,'na@*')

              and lower(right(Email_Address,3))<>'n/a'