
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Phone Number Formatting Question
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
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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",
//


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Subfield, keepchar, purgechar and replace will be useful functions to extract the data you need.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
you could also use regualr expressions for this task.
Here are some links on using RegEx in QlikView:
- http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/ by Barry Harmsen
- How to use regular expressions by Andrea Ghirardello
hope this helps
regards
Marco

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can use keepchar() like below
LOAD
*,
KeepChar(PhoneNumber, '1234567890exEX') AS PhoneNumber_Formatted
FROM DataSource;
Hope this helps you.
Regards,
Jagan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
If possible post sample phone numbers with expected output.
Use combination of subfield(),KeepChar() and if-else.
Regards
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI Margus,
If your phone number is first 10 digits then why don't you use LEFT(PhoneNumber,10) as PhoneNumber?
Thanks,
Vamsi

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dan Magnus,
i got solution for your requirement,Used sub field function in script.
Please find attached QVW.
Thanks,
Vamsi

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about turning it around and using keepchar()?
KeepChar(HomePhone,'0123456789EX')
-Rob

- « Previous Replies
-
- 1
- 2
- Next Replies »