topic Cell Phone validation check in QV in QlikView

Cell Phone validation check in QV

gerhardl — Mon, 11 Apr 2011 16:35:54 GMT

Hi,

I am using our Daily Account Extract in a new QV file I am building.

One thing I've done is create a bookmark based on numerous selections - this is a selection of customers I am sending a marketing message to on a monthly basis.

It works perfectly except for one thing I have to then do manually in excel after sending the selections to excel.

My selections are based on about 10 different fields. Then I send it to excel and I there I check that the customers have valid cell phone numbers.

In excel I do the following:

Cell Phone must be 15 or 16 characters.
Must start with +27(0) or +264(0).

Is there any way I can do this in QV? Ideally I would like to create a new field called "Valid Number". Then, if it meets the above criteria it must be Yes, and if not it must be No. This way I can just thsi field as "Yes" to my bookmarked selection.

Does this make sense?

Also, before I text the customers, I change the international format to the format our SMS interface can understand. I do this by removing the +27(0) or the +264(0). So in a new excel colum I say =right(A1,9) - (I just need the right 9 characters of the number). then I replace the international numbers with these 9 numbers. Can I somehow do this in my script already?

I know this is a two-part question; help in either or both would be great!

Thanks,

Cell Phone validation check in QV

IAMDV — Mon, 11 Apr 2011 17:05:28 GMT

Hi,

If this is not very urgent. I can look at this tonight... Also please post some sample data for testing purpose.

Cheers - DV

Cell Phone validation check in QV

prieper — Mon, 11 Apr 2011 21:09:38 GMT

Give it a try with

IF((LEN(PhoneNumber) = 15 OR LEN(PhoneNumber) = 16) AND (LEFT(PhoneNumber, 6) = '+27(0)' OR LEFT(PhoneNumber, 7) = '+27(0)',
'YES', 'NO') AS NumberIsValid

HTH
Peter

Cell Phone validation check in QV

rwunderlich — Mon, 11 Apr 2011 22:26:01 GMT

You can do very robust phone number validation in script using regular expression testing. See the QV Cookbook example "Regular Expression pattern matching function."