Extract section of a string

pbriggs11 — Sat, 16 Nov 2024 02:03:36 GMT

Hello all,

Within a field in the script, I'm trying extract a small section that includes a client ID (Underlined in the below example). As you can see, the number of leading zeros can differ as client ID's range from 4 to 9 digits, and never start with 0. After the client ID ends in the string, '-' appears. '-' at times appears for a second time in some of the records that have an account name that includes '-'. I've tried a number of combinations of left/index combinations but cannot quite get there, help please! I was close when indexing as such:

left(REPLACE(LTRIM(REPLACE(Description,'0',' ')),' ','0'),Index(Description,'-',-1)-Index(Description,'-',+1))

Data:

0000544056-01 Gross Wages Cost
0000544056-02 FICA ER Cost
0000544056-3
0000544056-03 Medicare ER Cost
0000544056-04 FUTA ER Cost
0000544056-5
0000544056-33 401k Payable - S
0000544056-33 Medcom Flex Paya
0000001655-30 SUTA Payable
0000001655-32 W/C - Zurich Pol
0000001655-33
0000001655-33 401k Payable - S
0000001655-33 Garnishments Pay
0000001655-33 Other Health Ins
0000001655-34
0000001655-34 401k Payable - S
0000001655-34 Florida Blue Hea
0000001655-51 Accounts Receiva
0000001655-54 Gross Wages Reve
0000001655-55 FICA Revenue

Re: Extract section of a string

sunny_talwar — Fri, 01 Nov 2019 14:43:15 GMT

How about this

Num(Num#(SubField(FieldName, '-', 1)))

Re: Extract section of a string

pbriggs11 — Fri, 01 Nov 2019 14:48:36 GMT

Thanks for the accurate and prompt reply Sunny, much appreciated!!!

topic Extract section of a string in QlikView

Extract section of a string

Re: Extract section of a string

Re: Extract section of a string