topic Re: Extract Phone Numbers from description in QlikView

Extract Phone Numbers from description

Dulguun1998 — Sat, 16 Nov 2024 03:35:30 GMT

I need to extract phone numbers from the transaction description.

My description includes:

TRF=001646195032-**********XX8741-99072741

TRF=001646187958-**********XX2062-ATM1098>UB 99995075À

99296772 ÕÀÀÍÀÀÑ: 020000 ÌßÃÌÀÐÑ¯ÐÝÍ ÑÀÍËÈÍ

My goal is to extract 8 digits phone numbers () from this description without the other unnecessary numbers.

I tried keepchar(DESCRIPTION,'0123456789') this method but my phone numbers keep mixing up with other numbers.

How can i extract only phone numbers

Can someone help me with this? I really appreciate it.

Re: Extract Phone Numbers from description

anushree1 — Tue, 14 May 2019 10:12:00 GMT

In this scenario, we must check what qualifies as a phone number, like does it occur at the start or end or does it have any preceding or succeeding unique symbols if not all the numbers that appear in description will be returned

Re: Extract Phone Numbers from description

Dulguun1998 — Wed, 15 May 2019 01:47:53 GMT

I have 4 different phone operators that start with unique numbers.

Unitel Operator: Phone number starts with 80, 86, 88, 89. For example 80807863, 86784531

Skytel Operator: Phone number starts with 90, 91, 96. For example 90958745, 91547843

Mobicom Operator: Phone number starts with 85, 94, 95, 99. For example 85783145, 95784121

Gmobile Operator: Phone number starts with 53, 83, 93, 97, 98. For example 98457412, 97453256

Re: Extract Phone Numbers from description

Anil_Babu_Samineni — Wed, 15 May 2019 02:46:04 GMT

Can you please post reality rows along with output needed from description field?

Re: Extract Phone Numbers from description

Dulguun1998 — Wed, 15 May 2019 02:56:44 GMT

For example :

in the Description:

89061500 ÕÀÀÍÀÀÑ: 320000 ÑÈÏÈÌÅÄÈÀ ÕÕÊ

94050119 ÕÀÀÍÀÀÑ: 340000 ÒÓÍÃÀËÀÃ ÖÝÍÃÝËÌÀÀ

EB-16:45:44 (95920909) - tulbur 95920909

TRF=001619257110-**********XX6378-99841895

My output:

89061500

94050119

95920909

99841895

Re: Extract Phone Numbers from description

jonathandienst — Wed, 15 May 2019 06:10:49 GMT

This script fins the phone numbers:

Input: LOAD RecNo() as ID, Descrip Inline [ Descrip 89061500 ÕÀÀÍÀÀÑ: 320000 ÑÈÏÈÌÅÄÈÀ ÕÕÊ 94050119 ÕÀÀÍÀÀÑ: 340000 ÒÓÍÃÀËÀÃ ÖÝÍÃÝËÌÀÀ EB-16:45:44 (95920909) - tulbur 95920909 TRF=001619257110-**********XX6378-99841895 TRF=001646195032-**********XX8741-99072741 TRF=001646187958-**********XX2062-ATM1098>UB 99995075À 99296772 ÕÀÀÍÀÀÑ: 020000 ÌßÃÌÀÐÑ¯ÐÝÍ ÑÀÍËÈÍ ]; //List of valid phone prefixes Set vMatch = 80,86,88,89,90,91,96,85,94,95,99,53,83,93,97,98; Output: //Filter for telephone numbers LOAD ID, Descrip, AllNumbers as Phone Where Match(Num(Left(AllNumbers, 2)), $(vMatch)) ; //Split out the tokens LOAD ID, Descrip, SubField(CleanNumbers, ' ') as AllNumbers ; //Replace all delimiters with spaces and discard other non-numerics LOAD ID, Descrip, KeepChar(Replace( Replace( Replace(Descrip, '=', ' '), '-', ' '), '>', ' '), '0123456789 ' ) as CleanNumbers Resident Input; //Clean up DROP Table Input;

Output:

Re: Extract Phone Numbers from description

tresB — Wed, 15 May 2019 06:43:03 GMT

Another way could be using a loop through the string like:

//List of valid phone prefixes Set vMatch = 80,86,88,89,90,91,96,85,94,95,99,53,83,93,97,98; Load * Where Not IsNull(PhNum) ; Load ID, Description, Mid(Description, IterNo(),8), IterNo(), If(IsNum(Replace(Mid(Description, IterNo(),8),' ','*')) and Match(Mid(Description, IterNo(),2), $(vMatch)), Mid(Description, IterNo(),8)) as PhNum While IterNo()<=Len(Description)-7; Input: LOAD RecNo() as ID, Description Inline [ Description 89061500 ÕÀÀÍÀÀÑ: 320000 ÑÈÏÈÌÅÄÈÀ ÕÕÊ 94050119 ÕÀÀÍÀÀÑ: 340000 ÒÓÍÃÀËÀÃ ÖÝÍÃÝËÌÀÀ EB-16:45:44 (95920909) - tulbur 95920909 TRF=001619257110-**********XX6378-99841895 TRF=001646195032-**********XX8741-99072741 TRF=001646187958-**********XX2062-ATM1098>UB 99995075À 99296772 ÕÀÀÍÀÀÑ: 020000 ÌßÃÌÀÐÑ¯ÐÝÍ ÑÀÍËÈÍ ];

Using Jonathan's script above upto some extend.

Re: Extract Phone Numbers from description

anushree1 — Wed, 15 May 2019 06:42:36 GMT

Hi Jonty,

I was checking another thread which seems to a question on generating missing values.

I have always done generating values from top to bottom but not able to find the logic to do it from bottom to top could you please have a look at https://community.qlik.com/t5/QlikView-Scripting/Qlik-Sense-fill-cells-from-the-bottom-to-the-top/m-p/1579781#M95229 and provide some solution there .

I have tagged you as well on that post

Re: Extract Phone Numbers from description

Anil_Babu_Samineni — Wed, 15 May 2019 06:56:09 GMT

Is there any situation will come row as below?

TRF=001619257110-**********XX6378-9984189599841895

Re: Extract Phone Numbers from description

Dulguun1998 — Wed, 15 May 2019 07:21:50 GMT

The output should be the last one

99841895

Re: Extract Phone Numbers from description

Dulguun1998 — Wed, 15 May 2019 08:30:52 GMT

I really appreciate your response.

How can I load my Descriptions from the QVD file instead of loading from Inline?

Re: Extract Phone Numbers from description

Dulguun1998 — Fri, 17 May 2019 02:12:11 GMT

Thank you for your response.

Your script works perfectly except this Description ( EB-14:19:01 (89789086) - {mb:ps}88483240 ).

My output should be this number 88483240 but instead, my output showing me this 9789086).

How can I fix this

I really appreciate it.