Skip to main content
Solved

DQ Rule Synchronise name with initials

  • December 9, 2024
  • 2 replies
  • 16 views

JTH
Data Pioneer

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.

Best answer by AKislyakov

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

View original
Did this topic help you find an answer to your question?

2 replies

Forum|alt.badge.img+2
  • Ataccamer
  • 145 replies
  • Answer
  • December 9, 2024

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


JTH
Data Pioneer
  • Author
  • 16 replies
  • December 10, 2024

@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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings