Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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",
//
Subfield, keepchar, purgechar and replace will be useful functions to extract the data you need.
Hi,
you could also use regualr expressions for this task.
Here are some links on using RegEx in QlikView:
hope this helps
regards
Marco
Hi,
You can use keepchar() like below
LOAD
*,
KeepChar(PhoneNumber, '1234567890exEX') AS PhoneNumber_Formatted
FROM DataSource;
Hope this helps you.
Regards,
Jagan.
Hi,
If possible post sample phone numbers with expected output.
Use combination of subfield(),KeepChar() and if-else.
Regards
HI Margus,
If your phone number is first 10 digits then why don't you use LEFT(PhoneNumber,10) as PhoneNumber?
Thanks,
Vamsi
Hi Dan Magnus,
i got solution for your requirement,Used sub field function in script.
Please find attached QVW.
Thanks,
Vamsi
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.
How about turning it around and using keepchar()?
KeepChar(HomePhone,'0123456789EX')
-Rob