Qlik Community

App Development

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

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
venkatbza
Creator
Creator

Extract number from string

Hi All,

I have a comments column and in that I have service number and along with some comments and dates. In that I have to strip only service number. service numbers are mostly having 8 digits but few cases service numbers are 7 and 9 digits. 

sample data posted below, help me out how I can extract only numbers(highlighted red color) from a given column.

Comments
DLL DISC / 02.09.2021/ 30364307/abc
LEASE DISC / 11.23.2020/10117134/def
LEASE DISC / 11.23.2020/10127588/Integr
LEASE DISC / 10101466, 10143520 / ADFC
LEASE DISC / 10111445 / 80800005
LEASE DISC / 10131743, 10149069 / not
LEASE DISC / 10133124/ 10146144 QUALITY
LEASE DISC / 10145033 / 10118692 / Comm
LEASE DISC / 10146155/10135854 / Avffd
LEASE DISC / 10147220/10104449 /kjkdj
LEASE DISC / 10147431, 10147394 / hdhdh
LEASE DISC / 30335157 / 1518833 hhdyyy
LEASE DISC /10104449, 10147220 RYETBDJ
LEASE DISC/10105402-10144528/TRHJDSNJ
LEASE DISC/10124511-10147275/HTHRJHJ
LEASE DISC/10143314-10144617/hdjhjdhk
PMC DISC / 10141863, 10147184 / kokoo
PMC DISC 91000010 /10141863/hhdj ddd
PMC DISC 91000010 /10145554/jjjdjd'sss
PMC/ADV MED HOMECARE/10131042 10144358
PMC/DYNAMIC HEALTHCARE/10146887 10101760
WIRE FEE / 08-26-2020/10106466/MEDICAL
WIRE FEE/03.26.2021/10141187/TG OXYGEN
10100465 Lease Disc
10101498 Lease Disc
10101760 Lease Disc
10123751 / hddyytr nnn
10125787 Lease Disc
10126312 /reed nhhd lllj
10127600 Lease Disc
10140316 Lease Disc
10146887 Lease Disc
ACH / 10101498 / hhdjh jkeebeel
ACH 91000016/AUGUST PREFUND/REBATE
WIRE FEE/03.26.2021/955843261/TG OXYGEN
LEASE DISC / 1014368 / bvcded Consulting

 

Thanks

Labels (1)
1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

I would approach this by first normalizing the character that is separating the values and then using the subfield() function to extract the parts.  This does mean that you have to anticipate the characters that are used to separate the fields.  Something like this:

NoConcatenate

Comments:

Load Load Key, replace(replace(replace(replace(Comments,'#',','),' ',','),'/',','),'-',',') as Comments

Resident [table with comments];

CommentParts:

Load subfield(Comments,',') as CommentPart
Resident Comments;

NoConcatenate

ServiceNumber:

Load CommentPart as ServiceNumber

Resident CommentParts
where IsNum(CommentPart) and len(CommentPart) >= 7 and len(CommentPart) <=9;

drop table CommentParts;

If you need to have the Service number in the same table as the original table, you can in the key field so that you can join it back to the original table.

 

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

I would approach this by first normalizing the character that is separating the values and then using the subfield() function to extract the parts.  This does mean that you have to anticipate the characters that are used to separate the fields.  Something like this:

NoConcatenate

Comments:

Load Load Key, replace(replace(replace(replace(Comments,'#',','),' ',','),'/',','),'-',',') as Comments

Resident [table with comments];

CommentParts:

Load subfield(Comments,',') as CommentPart
Resident Comments;

NoConcatenate

ServiceNumber:

Load CommentPart as ServiceNumber

Resident CommentParts
where IsNum(CommentPart) and len(CommentPart) >= 7 and len(CommentPart) <=9;

drop table CommentParts;

If you need to have the Service number in the same table as the original table, you can in the key field so that you can join it back to the original table.

 

venkatbza
Creator
Creator
Author

Hi GaryGiles,

Thanks for your suggestion , it works for me.