Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

raynac
Contributor

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

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

raynac
Contributor

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.

Community Browser