Creating a loop within Script for parsing a substring

RWilliams92 — Thu, 10 Mar 2022 15:34:44 GMT

I have part of my load script below; the aim is to parse out part of a string (Contract Name) with a cleaned number (e.g. 10, 20, 30 etc.). As you can see, there's a few different combinations within the string itself (it's not in standardised format so the substring can appear anywhere). The numbers however go from 10 all the way up to 500+ (in increments of 10). Without obviously repeating the below over and over again per line, is there a way I can get it to loop a single line?

if(WildMatch("Contract Name",'*_0010_*','*_0010-*','*-0010_*','*-0010-*','*_10_*')>0,'10',
if(WildMatch("Contract Name",'*_0020_*','*_0020-*','*-0020_*','*-0020-*','*_20_*')>0,'20',
if(WildMatch("Contract Name",'*_0030_*','*_0030-*','*-0030_*','*-0030-*','*_30_*')>0,'30',
if(WildMatch("Contract Name",'*_0040_*','*_0040-*','*-0040_*','*-0040-*','*_40_*')>0,'40',
if(WildMatch("Contract Name",'*_0050_*','*_0050-*','*-0050_*','*-0050-*','*_50_*')>0,'50',
if(WildMatch("Contract Name",'*_0060_*','*_0060-*','*-0060_*','*-0060-*','*_60_*')>0,'60',
if(WildMatch("Contract Name",'*_0070_*','*_0070-*','*-0070_*','*-0070-*','*_70_*')>0,'70',
if(WildMatch("Contract Name",'*_0080_*','*_0080-*','*-0080_*','*-0080-*','*_80_*')>0,'80',
if(WildMatch("Contract Name",'*_0090_*','*_0090-*','*-0090_*','*-0090-*','*_90_*')>0,'90',
if(WildMatch("Contract Name",'*_00100_*','*_00100-*','*-00100_*','*-00100-*','*_100_*')>0,'100',
if(WildMatch("Contract Name",'*_00110_*','*_00110-*','*-00110_*','*-00110-*','*_110_*')>0,'110',
if(WildMatch("Contract Name",'*_00120_*','*_00120-*','*-00120_*','*-00120-*','*_120_*')>0,'120',
if(WildMatch("Contract Name",'*_00130_*','*_00130-*','*-00130_*','*-00130-*','*_130_*')>0,'130',
if(WildMatch("Contract Name",'*_00140_*','*_00140-*','*-00140_*','*-00140-*','*_140_*')>0,'140',
if(WildMatch("Contract Name",'*_00150_*','*_00150-*','*-00150_*','*-00150-*','*_150_*')>0,'150',
if(WildMatch("Contract Name",'*_00160_*','*_00160-*','*-00160_*','*-00160-*','*_160_*')>0,'160',
if(WildMatch("Contract Name",'*_00170_*','*_00170-*','*-00170_*','*-00170-*','*_170_*')>0,'170',
if(WildMatch("Contract Name",'*_00180_*','*_00180-*','*-00180_*','*-00180-*','*_180_*')>0,'180',
if(WildMatch("Contract Name",'*_00190_*','*_00190-*','*-00190_*','*-00190-*','*_190_*')>0,'190',
if(WildMatch("Contract Name",'*_00200_*','*_00200-*','*-00200_*','*-00200-*','*_200_*')>0,'200',
null()))))))))))))))))))))

Thanks

Re: Creating a loop within Script for parsing a substring

RsQK — Fri, 11 Mar 2022 07:51:23 GMT

Hey, maybe this:

Contracts: LOAD * INLINE [ Contract Name AA_0010-1-XYZ BB-0100_01 CC_0230_AA DD-0020-BB_AA EE_BB_1-0030_A-BB ]; temp_contract_list: LOAD DISTINCT "Contract Name", SUBFIELD(SUBFIELD("Contract Name",'_'),'-') AS combination RESIDENT Contracts; temp_contract_list2: LOAD "Contract Name", NUM(EVALUATE(combination)) AS combination_eval RESIDENT temp_contract_list; DROP TABLE temp_contract_list; temp_comb: LOAD FIELDVALUE('combination_eval',RECNO()) AS combination_eval AUTOGENERATE FIELDVALUECOUNT('combination_eval'); INNER JOIN (temp_contract_list2) LOAD combination_eval RESIDENT temp_comb WHERE combination_eval > 9; DROP TABLE temp_comb; LEFT JOIN (Contracts) LOAD "Contract Name", MIN(combination_eval) AS Number RESIDENT temp_contract_list2 GROUP BY "Contract Name"; DROP TABLE temp_contract_list2;

topic Creating a loop within Script for parsing a substring in App Development

Creating a loop within Script for parsing a substring

Re: Creating a loop within Script for parsing a substring