Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can QV detect characters within a record ?

Hi,

Say for example that I have a field named Email_Address with many records and i wanted to identify incorrect email addresses.

For example I may want to count the number of @ characters in each records in order to flag any instances with more than one

Any thought appreciated

Thanks

Stuart

14 Replies
Not applicable
Author

fair point

MarcoWedel

Hi,

to verify email adresses you could use a regular expression solution in QlikView like

How to use regular expressions ( by Andrea Ghirardello

and test your email adresses with patterns found e.g. here:

php - Using a regular expression to validate an email address - Stack Overflow

the result could look like:

QlikCommunity_Thread_131255_Pic1.JPG.jpg

hope this helps

regards

Marco

MarcoWedel

one possible implementation:

QlikCommunity_Thread_131255_Pic2.JPG.jpg

QlikCommunity_Thread_131255_Pic3.JPG.jpg

tabEmailAddress:

LOAD *,

    If(MatchRegExp(eMailAddress, '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$'),'valid','invalid') as Valid

INLINE [

    eMailAddress

    john.doe@anydomain.com

    jane.doe@anydomain.com

    bob.doe@anydomaincom

    billdoe@anydomain.com

    stevedoe@anydomaincom

    no@no.de

    superman@@krypton.com

    supermankrypton.com

    mr.spock@vulkan.universe

];

VBS module:

Function MatchRegExp(targetString, patternSring)

  Dim re

  Set re = New RegExp

  re.Pattern = patternSring

  re.Global = False

  re.IgnoreCase = False

  MatchRegExp = re.Test(targetString)

End Function

hope this helps

regards

Marco

MarcoWedel

or like this:

tabEmailAddress:

LOAD *,

    If(IsEmailAddress(eMailAddress),'valid','invalid') as Valid

INLINE [

    eMailAddress

    john.doe@anydomain.com

    jane.doe@anydomain.com

    bob.doe@anydomaincom

    billdoe@anydomain.com

    stevedoe@anydomaincom

    no@no.de

    superman@@krypton.com

    supermankrypton.com

    mr.spock@vulkan.universe

];

Function IsEmailAddress(targetString)

  Dim re

  Set re = New RegExp

  re.Pattern = "^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$"

  re.Global = False

  re.IgnoreCase = False

  IsEmailAddress = re.Test(targetString)

End Function

hope this helps

regards

Marco

MarcoWedel

other RegExps found in the net, that claim to validate email addresses.

maybe someone would like to give it a try and post some comparison?:

\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b


^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$


^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$



(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])


[-0-9a-zA-Z.+_]+@[-0-9a-zA-Z.+_]+\.[a-zA-Z]{2,4}


^(?(")(".+?(?<!\\)"@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9][\-a-z0-9]{0,22}[a-z0-9]))$


^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$;



hope this helps


regards


Marco