Special characters in a number field

[mcoetzee]

Hi, 

I have a field (SMSNo) field where cell numbers are captured in, but i have occasions where people capture  special characters in, i want to created an if statement in my script to identify just the client records  whom has a special character in the (SMSNo) field and where the number does not contain 10 digits. I also want to eliminate the fields that are blank.

Thank you

[rwunderlich · Accepted solution]

You can use PurgeChar() to remove the valid characters and test the length of remaining

if( Len(PurgeChar(SmsNo, '0123456789')) = 0  and Len(SmsNo) = 10, 'Y', 'N') as [Valid?]

If you need to get more sophisticated, you may consider using regular expressions to validate phone numbers as shown in this example. 

Qlikview Cookbook: Regular Expression Pattern Matching http://qlikviewcookbook.com/recipes/download-info/regular-expression-pattern-matching/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com