Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

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!

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Can Data Be "Scrubbed"?

Use:

keepchar (phone_number, '1234567890') as Phone_Number

View solution in original post

4 Replies
mwoolf
Honored Contributor II

Re: Can Data Be "Scrubbed"?

look at purgechar in help

MVP & Luminary
MVP & Luminary

Re: Can Data Be "Scrubbed"?

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

-Rob

Not applicable

Re: Can Data Be "Scrubbed"?

Use:

keepchar (phone_number, '1234567890') as Phone_Number

View solution in original post

Partner
Partner

Re: Can Data Be "Scrubbed"?

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.