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:
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!
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.