3 Replies Latest reply: Jul 28, 2017 10:32 AM by Brian Head RSS

    Find phone numbers with nonconforming format

    Brian Head

      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,


        • Re: Find phone numbers with nonconforming format
          Aurélien Martinez



          I think this solution works


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

            • Re: Find phone numbers with nonconforming format
              Brian Head

              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!

            • Re: Find phone numbers with nonconforming format
              Mark Ritter

              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.