topic Re: Grab a number from a text string in QlikView

Grab a number from a text string

bnelson111 — Wed, 08 Dec 2021 16:15:28 GMT

Field Name = BillReqText

Below is the text string that changes from the field BillReqText.

I wish to grab the 5 digit number (62467) which also changes however will always be 5 digits and may not start with a 6 however will not start with anything lower than 4.

"PLRF32444EEEEEC - rrr tell REGDD 1L NBC HFSS

Need this NUMBER 62467

2 INVOICED 08/10 - ADD 3 CHARGED 01/12 - TOTAL 5

PO 17222798/OJDDDW"

Thanks Brett

Re: Grab a number from a text string

Lisa_P — Wed, 08 Dec 2021 23:10:45 GMT

Try this:

Subfield(TextBetween(BillReqText, 'NUMBER ', 'INVOICED'), ' ', 1)

Which is searching for the text between NUMBER and INVOICED and then only taking the first part before the space.

Re: Grab a number from a text string

MarcoWedel — Thu, 09 Dec 2021 21:10:18 GMT

Hi,

as a front end expression implementing all your conditions might be impractical, I would suggest extracting these numbers in the script, e.g. like this:

mapNumLet: Mapping LOAD Chr(RecNo()), If(IsNum(Chr(RecNo())),'0','_') AutoGenerate 65535; tabBillReqText: LOAD *, MapSubString('mapNumLet',BillReqText) as BillReqTextFormat Inline [ BillReqText PLRF32444EEEEEC - rrr tell REGDD 1L NBC HFSS Need this NUMBER 62467 Need this NUMBER 52467 2 INVOICED 08/10 - ADD 3 CHARGED 01/12 - TOTAL 5 PO 17222798/OJDDDW another number 44444 amidst some text one number at the end 98765 Same number in different text 98765 55555 number at the beginning two numbers 56788 in one 56789 text? oops 1234 only 4 digits 12345 is too small 66666 starts with 6 no number here ]; tabBillReqTextNum: LOAD * Where BillReqNum >= 40000 and not BillReqNum like '6*'; LOAD BillReqText, Mid(BillReqText,Index('_'&BillReqTextFormat&'_','_00000_',IterNo()),5) as BillReqNum, IterNo() as BillReqNumSeq Resident tabBillReqText While IterNo() <= SubStringCount('_'&BillReqTextFormat&'_','_00000_');

hope this helps

Marco