How are you "losing" your customers? Are you making a single table in your load script and then only end up with four rows. You say that you want four rows in your requirements - so I am not sure what you are asking for...
But let give you a hint about the TextBetween() - it will return an empty string so if you check for an empty string you could replace the value with a special number like -1 or a running number that is negative for those that dont' have a Payer number... then you can retain all Payers even if they don't have a payer number.
What you do should be based on some clear requirements.
Here is an approach that might work for you:
Payer:
LOAD
PayerName1,
If ( [Payer number] = '' , -AutoNumber(RowNo(),'Payer Number') , [Payer number] ) AS [Payer number]
;
LOAD
SubField(Payer1,'(',1) AS PayerName1
, TextBetween(Payer1,'(',')') AS [Payer number]
INLINE [
Payer1
SORELEC (4758741)
SOVE ()
Process & Plant Sales Ltd ()
PRODEX NORTH EUROPEAN (177635)
ABS-5006 (67548909)
FINCH-020 (566365657)
] (delimiter is \t);
Payer2:
LOAD * INLINE [
Payer Payer number
SORELEC 4758741
PRODEX NORTH EUROPEAN 177635
ABS-5006 67548909
FINCH-020 566365657
] (delimiter is \t);
