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)

      1050

      8010

      12350

      07830

      80150

      etc.

       

      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?

      Thanks