topic Can QV detect characters within a record ? in QlikView

Can QV detect characters within a record ?

Fri, 29 Aug 2014 11:45:46 GMT

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

Re: Can QV detect characters within a record ?

Fri, 29 Aug 2014 11:53:14 GMT

Can you check this site

Regular expressions in the load script - The Qlik Fix! The Qlik Fix!

Re: Can QV detect characters within a record ?

Anonymous — Fri, 29 Aug 2014 11:55:16 GMT

you can count the number of @ with substringcount

substingcount(string,'@') gives you th enumber of occurances of the sign @

Re: Can QV detect characters within a record ?

Fri, 29 Aug 2014 11:56:43 GMT

Hi Stuart,

you could use substringcount for this

If(substringcount([Email Field], '@')>1, 'Your Flag Here') As Flag

hope that helps

Joe

Re: Can QV detect characters within a record ?

Fri, 29 Aug 2014 12:15:08 GMT

Hi,

That one seems to count the overall count of instances. What I was interested in was counting instances where we had an incorrect email like

test@@test.com

Any thoughts?

Re: Can QV detect characters within a record ?

Anonymous — Fri, 29 Aug 2014 12:21:52 GMT

I donot think that there is a sinple solutio

you can test for any combinations which you know as you stated and using

substringcount(string,'@@') etc.

I know ist annoying. But maybe another Person has a better idea

Re: Can QV detect characters within a record ?

martynlloyd — Fri, 29 Aug 2014 12:27:33 GMT

Rudolf and Joe have got the solution.

Any email address with more than one @ in the string is invalid, so I can't see a down side to Joes IF statement...

Regards,

Re: Can QV detect characters within a record ?

tresB — Fri, 29 Aug 2014 12:31:33 GMT

Isn't that essentially gives a logic like - 'A mail address is incorrect if not it contains only one @ character' ? If agreed, you can easily put it like:

Load

If(SubstringCount( MailAddfield, '@') =1, 'Correct', 'Incorrect') as Flag

Isn't it?

Re: Can QV detect characters within a record ?

Fri, 29 Aug 2014 12:33:43 GMT

What Martyn said really, that should work fine, regardless of being together or not, having two '@' is invalid.

If you want to start building multiple rules for what is considered valid, then that would probably need something a bit more complex

Re: Can QV detect characters within a record ?

tresB — Fri, 29 Aug 2014 12:37:51 GMT

What if the count is zero?

Re: Can QV detect characters within a record ?

Fri, 29 Aug 2014 13:59:24 GMT

fair point

Re: Can QV detect characters within a record ?

MarcoWedel — Fri, 29 Aug 2014 16:37:10 GMT

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:

hope this helps

regards

Marco

Re: Re: Can QV detect characters within a record ?

MarcoWedel — Fri, 29 Aug 2014 23:01:54 GMT

one possible implementation:

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

Re: Re: Re: Can QV detect characters within a record ?

MarcoWedel — Sat, 30 Aug 2014 09:52:33 GMT

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

Re: Can QV detect characters within a record ?

MarcoWedel — Sun, 31 Aug 2014 12:00:33 GMT

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