Hello Group Members,
We may be called upon to test the count of letters (only A to Z and not special characters) in an Alpha-Numeric field. This analytic requirement may be part of validation checks on key fields as supported by Data Quality Management initiatives of the end user.
Let us delve into this post with an example. Hypothetically you have an Alpha-Numeric field titled 'Registration Number' which is CCCCCNNNNC (example - ABCDE1234F)
6 character representations coupled with 4 numerical representations.
Now in the given example, the Alpha-Numeric field needs to be tested for the presence of less than or more than 6 character representations which would indicate an invalid registration number. This test is being done at a global level on the entire field and not any part of the field.
This can be achieved in IDEA by applying a Direct Extraction with the criteria ~
@Len(@justletters(Registration Number)) <> 6
In the criteria above @justletters() will virtually extract all the letters (A to Z) and @len() will count these letters extracted.
Needless to say if you need the length as a separate field in IDEA, it can be done by appending a virtual numeric field titled 'Length Reg No' with the criteria ~
@Len(@justletters(Registration Number))
Can we visualize and discuss any other Data Quality Management checks possible through IDEA. Welcome Group Member inputs.
Best Regards
Group Admin Team