Thanks for the suggestion.
To elaborate more on the question;
In the table with the valid accountID format I have for instance:
And in the table with invalid data:
I need to match 01050 with 1050. That's why I used the $(vMapExpr(accountID)) from your cookbook.
But the next load I have valid: 80150 with match 80150.
I load all these tables in 1 load statement with a loop to process all tables.
I can imagine it's not posible to solve this because I'm only working with numbers. In that case I have to figure out a work around.
I created a solution. Easier than I first thoughed but this works.
First I load the table that has the Valid values.
I calculate the max length of that string and store that in variable length.
max(len(accountID)) as accountID_length
Resident [my table];
let length = peek('accountID_length', 0, 'AccountID_length');
Next I load the transaction with the Valid and/or Invalid values but I add
right(accountID, $(length)) as accountID
to that load. Preceding Invalid characters (numbers) are eliminated this way.
The downside of this is that as long as the Invalid values don't have a suffix added in the transaction this will work.