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

Find phone numbers with nonconforming format

Hi all,

I'm trying to figure out a way to find US phone numbers that do not fit the format XXX-XXX-XXXX. I am not wanting to change the format in Qlik, but rather identify them so we can update in our database (we use Qlik off of a copy of the database). For example, some have been entered as (XXX) XXX-XXXX, others as XXX XXX-XXXX, or XXXXXXXXXX, etc.

I haven't found any existing posts with this question, forgive me if I've missed any.

Any suggestions would be most appreciated.

Thank you,

Brian

1 Solution

Accepted Solutions
Not applicable
Author

Thank you, Aurelien. This code did almost what I needed and I was able to modify it slightly do so. Here's what ultimately did what I wanted:

if (len(SubField("Phone", '-', 1)) = 3 and len(SubField("Phone", '-', 2)) = 3 and

    len(SubField(" Phone", '-', 3)) = 4,1,0) as [homephoneformatz]

[next table]

if (not isnull("Phone") and [homephoneformatz]=0,1,0) as homephoneformat,

For some reason the "not" in front of "len" in the example code you provided wasn't working for me. In part it was the null values being identified that I didn't care about--I didn't mention that in my post. But, it seemed to be picking up valid format phone numbers too. Anyway the above combo works.

Many thanks for your help!

View solution in original post

3 Replies
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

I think this solution works

not (len(SubField(num, '-', 1)) = 3 and len(SubField(num, '-', 2)) = 3 and len(SubField(num, '-', 3)) = 4)

Help users find answers! Don't forget to mark a solution that worked for you!
rittermd
Master
Master

I don't think that there is an easy way to do this. 

The first thing that I would do is create your load script to check for certain things and only load them (your exceptions)

For example any phone numbers less than length of 12

Any phone numbers that contain '(' or ')'

Any phone numbers that do not contain a '-'

Then only bad phone numbers will be loaded into your app and you can export that list out to work on.

This will not catch every possibility of an error.  But should get most of them.

Not applicable
Author

Thank you, Aurelien. This code did almost what I needed and I was able to modify it slightly do so. Here's what ultimately did what I wanted:

if (len(SubField("Phone", '-', 1)) = 3 and len(SubField("Phone", '-', 2)) = 3 and

    len(SubField(" Phone", '-', 3)) = 4,1,0) as [homephoneformatz]

[next table]

if (not isnull("Phone") and [homephoneformatz]=0,1,0) as homephoneformat,

For some reason the "not" in front of "len" in the example code you provided wasn't working for me. In part it was the null values being identified that I didn't care about--I didn't mention that in my post. But, it seemed to be picking up valid format phone numbers too. Anyway the above combo works.

Many thanks for your help!