My requirement is to have one table where i have flag assigned to each record as
Payer
Payer number
Pflag
SORELEC
4758741
1
PRODEX NORTH EUROPEAN
177635
1
ABS-5006
67548909
1
FINCH-020
566365657
1
i tried the following expression num(trim(TextBetween(Payer.Name,'(',')',substringcount(Payer.Name,'(')))) but in my result table i am losing my two customers
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]
