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: 
raynac
Partner - Creator
Partner - Creator

Can Data Be "Scrubbed"?

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!

1 Solution

Accepted Solutions
Not applicable

Use:

keepchar (phone_number, '1234567890') as Phone_Number

View solution in original post

4 Replies
m_woolf
Master II
Master II

look at purgechar in help

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think mwoolf has the right idea, but the KeepChar() function may be easier to use for this case.

-Rob

Not applicable

Use:

keepchar (phone_number, '1234567890') as Phone_Number

raynac
Partner - Creator
Partner - Creator
Author

Thank you all so much!  I implemented both functions and laid the columns side by side.  They provided pretty much exactly the same results.  The Purge left in a couple of characters I didn't think to account for (someone entered a + as part of an overseas number), but I am fairly sure it would have worked long term.

I very much appreciate your assistance.