Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

drminaker
New Contributor III

Separating area code from phone number into new field

Hi everyone,

I'm trying to clean phone numbers by doing two things:

  • Removing anything that's not a number from the Phone number column - no problem I used this code:

           purgechar([Phone number], ')(-') as [Phone number]

  • Problem is, when the area code is in the Phone number column (like row 2), I want to remove it and add it to the Area code column (or into any other new field). This is where I'm stuck, I've tried various iterations of if(len, substring, purgechar and just can't get this to work.

Any help appreciated, as always. Thanks!


R.

Area codePhone number
14161234567
24161234567
3(416)1234567
1 Solution

Accepted Solutions

Re: Separating area code from phone number into new field

Sample script

Table:

LOAD Sno,

If(Len(Purgechar([Phone number], ')(-')) = 10, Right(Purgechar([Phone number], ')(-'), 7), Purgechar([Phone number], ')(-')) as [Phone number],

If(Len(Trim([Area code])) > 0, [Area code], If(Len(Purgechar([Phone number], ')(-')) = 10, Left(Purgechar([Phone number], ')(-'), 3))) as [Area code];

LOAD * INLINE [

    Sno, Area code, Phone number

    1, 416, 1234567

    2, , 4161234567

    3, , (416)1234567

];

Capture.PNG

8 Replies

Re: Separating area code from phone number into new field

Which is your actual data row.

Re: Separating area code from phone number into new field

In row no 2 how you differentiate 416 is the code and 1234567 is the phone number.

Re: Separating area code from phone number into new field

May be like this

If(Len(Purgechar([Phone number], ')(-')) = 10, Right(Purgechar([Phone number], ')(-'), 7), Purgechar([Phone number], ')(-')) as [Phone number]

Re: Separating area code from phone number into new field

And this for Area Code...

If(Len(Trim([Area Code])) > 0, [Area Code], If(Len(Purgechar([Phone number], ')(-')) = 10, Left(Purgechar([Phone number], ')(-'), 3))) as [Area Code]

Re: Separating area code from phone number into new field

Sample script

Table:

LOAD Sno,

If(Len(Purgechar([Phone number], ')(-')) = 10, Right(Purgechar([Phone number], ')(-'), 7), Purgechar([Phone number], ')(-')) as [Phone number],

If(Len(Trim([Area code])) > 0, [Area code], If(Len(Purgechar([Phone number], ')(-')) = 10, Left(Purgechar([Phone number], ')(-'), 3))) as [Area code];

LOAD * INLINE [

    Sno, Area code, Phone number

    1, 416, 1234567

    2, , 4161234567

    3, , (416)1234567

];

Capture.PNG

Re: Separating area code from phone number into new field

Some of the questions

1. What is the length of the Phone no. are they fix.

2. Open brackets close and open are in fix place

LOAD *,

PurgeChar( SubField([Phone number],')',1),'(') as [Area Code],

SubField([Phone number],')',-1) as [New Phone];

LOAD * Inline

[

Area code, Phone number

1 ,  416 1234567

2 ,  4161234567

3 , (416)1234567

];

Re: Separating area code from phone number into new field

If they are in fix Area Code = 3 and Number = 7

Then

Data:

LOAD *,

PurgeChar( SubField([Phone number],')',1),'(') as [Area Code],

SubField([Phone number],')',-1) as [New Phone];

LOAD * Inline

[

Area code, Phone number

1 ,  416 1234567

2 , 4161234567

3 , (416)1234567

];

NoConcatenate

Final:

LOAD

If(len([Area Code]) = 3, [Area Code]) as [Area Code],

if(len([New Phone]) = 7,[New Phone]) as [New Phone]

Resident Data;

DROP Table Data;

drminaker
New Contributor III

Re: Separating area code from phone number into new field

Apologies for the extreme delay on replying to this, but this code works perfect. Thanks so much!

R.

Community Browser