Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Can you please post reality rows along with output needed from description field?
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
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:
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.
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-... and provide some solution there .
I have tagged you as well on that post
Is there any situation will come row as below?
TRF=001619257110-**********XX6378-9984189599841895
The output should be the last one
99841895