topic Re: Email Address Validation in QlikView

Email Address Validation

gerhardl — Wed, 02 Oct 2013 14:03:00 GMT

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

Re: Email Address Validation

jonathandienst — Wed, 02 Oct 2013 14:31:13 GMT

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

Re: Email Address Validation

gerhardl — Thu, 03 Oct 2013 06:46:41 GMT

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?],

Re: Email Address Validation

jonathandienst — Thu, 03 Oct 2013 06:57:07 GMT

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

Re: Email Address Validation

gerhardl — Thu, 03 Oct 2013 07:10:03 GMT

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.

Re: Email Address Validation

jonathandienst — Thu, 03 Oct 2013 08:47:04 GMT

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

Re: Email Address Validation

gerhardl — Thu, 03 Oct 2013 08:59:06 GMT

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.