topic Re: Phone Number Formatting Question in QlikView

Phone Number Formatting Question

Wed, 28 Jan 2015 21:09:58 GMT

I'm importing phone numbers that have no standard formating. Some have (XXX), some have +999, some have "-", etc. I found the purgeChr() function, which helps immensely. However, many of the numbers have extensions, such as....

1234567890ext999 or

1234567890x444

Is it possible to remove everything after (and including) the "e" or the "x" in the examples above?

Thanks, Dan

Re: Phone Number Formatting Question

Anonymous — Wed, 28 Jan 2015 21:15:39 GMT

Heh, a familiar problem ... see this (original phone number here is field P):

// Phone
if(index(lower(P), 'x')>0,
// there is extension
if(len(keepchar(subfield(lower(P),'x',1),'0123456789'))=10,
//format with extension
'(' & left(keepchar(subfield(lower(P),'x',1),'0123456789'),3) & ')' &
' ' & mid(keepchar(subfield(lower(P),'x',1),'0123456789'),4,3) &
'-' & right(keepchar(subfield(lower(P),'x',1),'0123456789'),4) &
' x ' & subfield(lower(P),'x',2),
// leave data as is
P),
// there is no extension
if(len(keepchar(P,'0123456789'))=10,
// format without ext
'(' & left(keepchar(P,'0123456789'),3) & ')' &
' ' & mid(keepchar(P,'0123456789'),4,3) &
'-' & right(keepchar(P,'0123456789'),4),
// we'll see
P))
as "Work Phone",
//

Re: Phone Number Formatting Question

Colin-Albert — Wed, 28 Jan 2015 21:20:51 GMT

Subfield, keepchar, purgechar and replace will be useful functions to extract the data you need.

Re: Phone Number Formatting Question

MarcoWedel — Thu, 29 Jan 2015 00:18:29 GMT

Hi,

you could also use regualr expressions for this task.

Here are some links on using RegEx in QlikView:

hope this helps

regards

Marco

Re: Phone Number Formatting Question

jagan — Thu, 29 Jan 2015 03:20:27 GMT

Hi,

You can use keepchar() like below

LOAD

KeepChar(PhoneNumber, '1234567890exEX') AS PhoneNumber_Formatted

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Phone Number Formatting Question

PrashantSangle — Thu, 29 Jan 2015 04:06:15 GMT

Hi,

If possible post sample phone numbers with expected output.

Use combination of subfield(),KeepChar() and if-else.

Regards

Re: Phone Number Formatting Question

Thu, 29 Jan 2015 05:53:14 GMT

HI Margus,

If your phone number is first 10 digits then why don't you use LEFT(PhoneNumber,10) as PhoneNumber?

Thanks,

Vamsi

Re: Phone Number Formatting Question

Thu, 29 Jan 2015 06:18:36 GMT

Hi Dan Magnus,

i got solution for your requirement,Used sub field function in script.

Please find attached QVW.

Thanks,

Vamsi

Re: Phone Number Formatting Question

Tue, 03 Feb 2015 17:40:01 GMT

So the main formula I'm using is: PurgeChar(HomePhone,'()-.,:+/=|_~?*` '))

This is getting rid of the symbols I'm seeing. However, now I need to get rid of all alpha (letters) I'm seeing, except for E and X (for phone extensions...I need these letters to use the index function). Is there a way, besides listing each of the 24 letters in the formula above, to eliminate these letters?

Thanks.

Re: Phone Number Formatting Question

rwunderlich — Tue, 03 Feb 2015 18:41:28 GMT

How about turning it around and using keepchar()?

KeepChar(HomePhone,'0123456789EX')

-Rob

Re: Phone Number Formatting Question

jagan — Wed, 04 Feb 2015 04:07:58 GMT

Hi,

Did you tried the solution I provided earlier using KeepChar()?

LOAD

KeepChar(PhoneNumber, '1234567890exEX') AS PhoneNumber_Formatted

FROM DataSource;

Regards,

Jagan.

Re: Phone Number Formatting Question

Thu, 05 Feb 2015 22:58:53 GMT

Yes, KeepChar is a lot simpler naming a dozen nums/chars to keep rather than all those to purge. Thanks!

Re: Phone Number Formatting Question

jagan — Fri, 06 Feb 2015 02:22:27 GMT

Hi Dan,

If you got the answer you can close this thread by marking Correct and Useful answers to the posts which helps you in finding the solution.

Regards,

Jagan.