I have a column accountID which contains valid values in different formats and invalid values in (for now 1 format). I want to validate based on rules.
Valid accountID represenations are:
020 (a 0 is added with the num function to create 0020)
Invalid accountID representation is:
(this is the same value as the Valid accountID representation but with a preceding 0.)
I do not know in which data file (*) (multiple) accountID is formatted as valid of invalid.
The accountID will be linked to second table, related to each individual data file (*). This table has the correct value for accountID, for instance 01050 has (the valid) value 1050 in the second table.
I tested the pick and wildmatch function from rwunderlich and that works for the invalid accountID where I added a ? to the other table but this cannot be used not when the 5 characters accountID is loaded......