Skip to main content

Hi,

I have two columns: first name and initials.
 

# first_name initials
1 John J.
2 Robin Earl R.E.
3 Mark Tate Lynch M.T.L.
4 Bert Ern B.E.
5 Johan Flugg J.F.


Is there a DQ rule that can check if the two columns are correctly in synch in what they should be? I understand checking the initials format is one thing, but I actually want to check if it is correctly formatted based on the first_name column. Looking for a preferred solution in ONE Web.

Hi ​@JTH,

There are 2 solutions:
1. Using set.* functions

replace(set.mapExp(first_name, ' ', (x) { left(x,1)+'.' }), ' ', '')

This code works with first_name as a set of words separated by  (space). For each word in a set function left(word,1)+'.' is applied.

The result is a set of first letters with dot separated by space. The final replaces removes spaces.

2. Using Regular expressions

replace(substituteAll(@"((^ ])( ^ ]*)\b",'$1.',first_name),' ','')

Again, each word is replaced with first letter + dot and then spaces removed.

The first one is generally faster, the last one requires less Ata-specific knowledge

​@AKislyakov thank you! I've done the following in advanced expression:


first_name is not null
and initials is not null
and type_person = 'N' -- N = natural person
and replace(set.mapExp(first_name, ' ', (x) { left(x,1)+'.' }), ' ', '') is not in {initials}

Reply


Community Terms of Use & ConditionsCookie settings