Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Can QV detect characters within a record ?

hrlinder
Honored Contributor

Re: Can QV detect characters within a record ?

you can count the number of @ with substringcount

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

Not applicable

Re: Can QV detect characters within a record ?

Hi Stuart,

you could use substringcount for this

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

hope that helps

Joe

Not applicable

Re: Can QV detect characters within a record ?

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?

hrlinder
Honored Contributor

Re: Can QV detect characters within a record ?

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

martynlloyd
Contributor III

Re: Can QV detect characters within a record ?

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,

M.

MVP
MVP

Re: Can QV detect characters within a record ?

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?

Not applicable

Re: Can QV detect characters within a record ?

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

MVP
MVP

Re: Can QV detect characters within a record ?

What if the count is zero?

Community Browser