Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Email Address Validation

Hello,

What would be the best approach if I want to apply email validation in my load script? i.e. I want to create a new field called [Email Valid?], and it must have a YES value if it passes all of the checks and a NO value if it doesn't.

Is there a better way than a complicated IF/AND/OR type statement?

I want to apply these rules:

Address cannot contain the following characters '%[ &'',":;!+=\/()<>]%'
Address cannot start with the following characters '[@.-_]%'
Address cannot end with the following characters '%[@.-_]'
Address must contain at least one @ and one .
Address cannot contain two .. in a row
Address cannot contain two @ anywhere
Address cannot have @ and . next to each other
A .com address cannot end in .cm or.co
A .org address cannot end in .or
A .net address cannot end in .ne
A .za address cannot end in .z

Thanks,

Gerhard

1 Solution

Accepted Solutions
Highlighted

Gerhard

If you are basing this on the first script that I posted, then that only applies the rule to exclude .cm if .com also exists in the email address. The later query should mark these as invalid. as does your script.

Oh, I see that the * is missing for the last 4  values in the last Wildmatch. So corrected is

If(Len([Email address]) = 0,

     'None',

     If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

          And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

          And (SubStringCount([Email address], '@') = 1)

          And (WildMatch([Email address], '*..*','*@.*', '*.@', '*@@*', '*.cm', '*.co', '*.or', '*.ne', '*.z') = 0)

          , 'Valid', 'Invalid'

     )

) As [Email Valid?],

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
Highlighted

Gerhard

Well you can use regular expressions via vbscript function call, but I don't use those for performance reasons. Search here for "VBScript regular expressions".

Of you can do it IF/AND/OR, but its not too complicated.

If(Len(KeepChar(Address, '%[ &'',":;!+=\/()<>]') = 0)

  And FindOneOf(Left(Address, 1) & Right(Address, 1), '@.-_') = 0

  And SubStringCount(Address, '@') = 1

  And Index(Address, '..') = 0

  And WildMatch(Address, '*@.*', '*.@', '*@@*') = 0

  And (If(Index(Address, '.com') = 0 Or WildMatch(Address, '*.cm', '*.co') = 0))

  And (If(Index(Address, '.org') = 0 Or WildMatch(Address, '*.or') = 0))

  And (If(Index(Address, '.net') = 0 Or WildMatch(Address, '*.ne') = 0))

  And (If(Index(Address, '.za') = 0 Or WildMatch(Address, '*.z') = 0))

  , 'YES', 'NO'

) As [Email Valid?]

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator II
Creator II

Thanks Jonathan,

I had to make a few changes to make it work for me (my question and rule set was not perfectly clear - apologies).

  If([Email address] ='','None',

  If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

  And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

  And (SubStringCount([Email address], '@') = 1)

  And (Index([Email address], '..') = 0)

  And (WildMatch([Email address], '*@.*', '*.@', '*@@*') = 0)

  And (right([Email address],3)<>'.cm' )

  And (right([Email address],3)<>'.co' )

  And (right([Email address],3)<>'.or' )

  And (right([Email address],3)<>'.ne')

  And (right([Email address],2)<>'.z'), 'Valid', 'Invalid')) As [Email Valid?],

Highlighted

Gerhard

Then we can simplify it further:

If(Len([Email address]) = 0,

     'None',

     If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

          And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

          And (SubStringCount([Email address], '@') = 1)

          And (WildMatch([Email address], '*..*','*@.*', '*.@', '*@@*', '*.cm', '.co', '.or', '.ne', '.z') = 0)

          , 'Valid', 'Invalid'

     )

) As [Email Valid?],

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator II
Creator II

Hi Jonathan,

I now have a mismatch between mine and yours - they are emails ending with .co or .z or .ne

I have them as invalid and yours is valid - any idea why?

Also - the rule for them must be that the email cannot END in those. If I use wildmatch it is going to check if it CONTAINS them, which might cause problems.

For instance, this email is valid:  example.cm@gmail.com

but this is invalid: example.cm@gmail.cm

Also, South African email addresses end is .co.za   - so '.co' cannot be seen as invalid unless it is at the very end.

Highlighted

Gerhard

If you are basing this on the first script that I posted, then that only applies the rule to exclude .cm if .com also exists in the email address. The later query should mark these as invalid. as does your script.

Oh, I see that the * is missing for the last 4  values in the last Wildmatch. So corrected is

If(Len([Email address]) = 0,

     'None',

     If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

          And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

          And (SubStringCount([Email address], '@') = 1)

          And (WildMatch([Email address], '*..*','*@.*', '*.@', '*@@*', '*.cm', '*.co', '*.or', '*.ne', '*.z') = 0)

          , 'Valid', 'Invalid'

     )

) As [Email Valid?],

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
Creator II
Creator II

I was talking about your latter query - I completely missed the asterisk as well, working perfectly now. You just need another asterisk after *.@ as well:

And (WildMatch([Email address], '*..*','*@.*', '*.@*', '*@@*', '*.cm', '*.co', '*.or', '*.ne', '*.z')

Thanks for the help.