Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Cell Phone validation check in QV

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,

G

3 Replies
Highlighted
Luminary
Luminary

Cell Phone validation check in QV

Hi,

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

Cheers - DV

Highlighted
Honored Contributor II

Cell Phone validation check in QV

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

Highlighted
MVP & Luminary
MVP & Luminary

Cell Phone validation check in QV

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