Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
look at purgechar in help
I think mwoolf has the right idea, but the KeepChar() function may be easier to use for this case.
-Rob
Use:
keepchar (phone_number, '1234567890') as Phone_Number
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.