4 Replies Latest reply: Jun 15, 2012 5:25 PM by Rayna Curtis RSS

    Can Data Be "Scrubbed"?

    Rayna Curtis

      Hi all,

       

      I have a database with a phone_number field.  The problem is that it's not a number but a string field where any of the following are valid:

      (416) 555-1212

      416-555-1212

      416.555.1212

       

      You get the idea.

       

      Now, we have another table from a completely different place where that same phone number is always stored as 4165551212, and I have been asked to join records based on those two fields.

       

      So here is my question - is there a way in the script to "scrub" the data in the first field of any non-numeric characters so it will cleanly join with the second field?  I found a reference to "num(evaluate(phone_number), '##########')" but the phone numbers might be non-North-American, so we can't know how many characters might be in the field...and I get a "Garbage after expression" error when I try this.  (I am still running Qlikview 9, if that makes a difference.) 

       

      Any assistance would be much appreciated!  Thank you!