topic Re: Can Data Be "Scrubbed"? in QlikView

Can Data Be "Scrubbed"?

raynac — Fri, 15 Jun 2012 20:31:57 GMT

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!

Re: Can Data Be "Scrubbed"?

m_woolf — Fri, 15 Jun 2012 20:44:39 GMT

look at purgechar in help

Re: Can Data Be "Scrubbed"?

rwunderlich — Fri, 15 Jun 2012 20:56:40 GMT

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

-Rob

Re: Can Data Be "Scrubbed"?

Fri, 15 Jun 2012 21:00:57 GMT

Use:

keepchar (phone_number, '1234567890') as Phone_Number

Re: Can Data Be "Scrubbed"?

raynac — Fri, 15 Jun 2012 21:25:46 GMT

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.