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

Anonymous
Not applicable
Author

you can count the number of @ with substringcount

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

Not applicable
Author

Hi Stuart,

you could use substringcount for this

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

hope that helps

Joe

Not applicable
Author

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?

Anonymous
Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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.

tresesco
MVP
MVP

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
Author

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

tresesco
MVP
MVP

What if the count is zero?