7 Replies Latest reply: Oct 23, 2015 4:12 AM by Michiel van de Goor RSS

    Validate data with different allowed formats

    Michiel van de Goor

      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:


      01050, 08010

      (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......


      How could I validate the format in this case?