topic Re: Extract number from string in QlikView

Extract number from string

venkatbza — Mon, 07 Jun 2021 14:03:48 GMT

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

Re: Extract number from string

Or — Mon, 07 Jun 2021 14:15:51 GMT

PurgeChar() or KeepChar() should work here - note that the cases where there are two sets of numbers will result in one large number.

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/StringFunctions/PurgeChar.htm

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/StringFunctions/KeepChar.htm

Re: Extract number from string

venkatbza — Mon, 07 Jun 2021 16:22:13 GMT

Hi Master,

Thank you for reply. By using PurgeChar or KeepChar dates are also converting to numbers which I don't need.

Thanks.

Re: Extract number from string

Or — Mon, 07 Jun 2021 16:42:16 GMT

PurgeChar and KeepChar both return strings, not numbers.

You can explicitly use num() or text() as required to convert the results to a number or text interpretation.