Automate

Hi Community! 

I came across a usecase and want to share with you all!

Use Case: Our business rule is that the customer name should only have alphanumeric characters, spaces and '&'. Any other special characters are not permitted. 

My task was to demonstrate how we can help improve data quality and apply this business rule. We can approach this in 2 ways. 

1. Mass data cleansing (think Studio!) - Reactive
2. Process controls to stop bad data from entering (think Evolve!) - Proactive 

Studio approach - Mass Data Cleansing

Step 1- Query customer data records to find the bad ones (search for special characters in the customer name field) 

I picked a few special characters at random, obviously this can be enhanced to catch more of them! 

STUDIO TIP: How to use the ESCAPE keyword in your Query criteria. 

SQL treats % as a wildcard, but I want to look for any customer names that literally contain the character '%'.

In this case, you have to use the ESCAPE keyword. 

Here is a little help from the internet - https://docs.progress.com/bundle/datadirect-openaccess/page/topics/sqlref/escape-clause-in-like-operator.html

I am using $ as my escape character. This tells the query that any wildcard character that occurs after the escape character in the pattern string should be treated as a regular character.

Once I have my query results from SAP, I created a transaction script for the BP Tcode to update the customer name with only the permitted characters.

I added a validation for the customer name input field in my mapper using the "Allowed Values" option shown below. 

Here we are using pattern matching to permit only allowed characters using a regular expression. 

This regex ^[a-zA-Z0-9& ']+$ permits only alphabets, numbers, & and space. 

As you can see in the Run log below, the script will disallow bad values.

You can reference the attached scripts to run through this in Studio. 

Evolve approach - Data Quality Rules at Source

Using the rules for form fields in Evolve, we can use the same pattern matching (Regex approach) to flag errors at source and prevent bad data from entering the system. Here is an example of a validation rule on our form field for customer name. 

The regex ^[a-zA-Z0-9& ']+$ permits only alphabets, numbers, & and space.

This can be easily adapted to allow or disallow other characters. Consider using this for phone formatting as well. 

I hope you find this useful! Happy data cleansing! - JV

Scripts included below.

Hi Community! 

I came across a usecase and want to share with you all!

Use Case: Our business rule is that the customer name should only have alphanumeric characters, spaces and '&'. Any other special characters are not permitted. 

My task was to demonstrate how we can help improve data quality and apply this business rule. We can approach this in 2 ways. 

1. Mass data cleansing (think Studio!) - Reactive
2. Process controls to stop bad data from entering (think Evolve!) - Proactive 

Studio approach - Mass Data Cleansing

Step 1- Query customer data records to find the bad ones (search for special characters in the customer name field) 

I picked a few special characters at random, obviously this can be enhanced to catch more of them! 

STUDIO TIP: How to use the ESCAPE keyword in your Query criteria. 

SQL treats % as a wildcard, but I want to look for any customer names that literally contain the character '%'.

In this case, you have to use the ESCAPE keyword. 

Here is a little help from the internet - https://docs.progress.com/bundle/datadirect-openaccess/page/topics/sqlref/escape-clause-in-like-operator.html

I am using $ as my escape character. This tells the query that any wildcard character that occurs after the escape character in the pattern string should be treated as a regular character.

Once I have my query results from SAP, I created a transaction script for the BP Tcode to update the customer name with only the permitted characters.

I added a validation for the customer name input field in my mapper using the "Allowed Values" option shown below. 

Here we are using pattern matching to permit only allowed characters using a regular expression. 

This regex ^[a-zA-Z0-9& ']+$ permits only alphabets, numbers, & and space. 

As you can see in the Run log below, the script will disallow bad values.

You can reference the attached scripts to run through this in Studio. 

Evolve approach - Data Quality Rules at Source

Using the rules for form fields in Evolve, we can use the same pattern matching (Regex approach) to flag errors at source and prevent bad data from entering the system. Here is an example of a validation rule on our form field for customer name. 

The regex ^[a-zA-Z0-9& ']+$ permits only alphabets, numbers, & and space.

This can be easily adapted to allow or disallow other characters. Consider using this for phone formatting as well. 

I hope you find this useful! Happy data cleansing! - JV