ACL Character Function – Alltrim() Function

Welcome back to the blog series ‘Audit Command Language Tutorials for Beginners’. At this stage in the series, we are discussing the numerous character functions that are available in the Audit Command Language to manipulate and transform character fields.

In this post, we are going to take a look at the ALLTRIM function. This function solves a problem that is more often than not, the root cause of most issues faced while performing any analysis. To get correct results from most functions, it is critical that all extra spaces (leading and trailing) are removed from the field before any transformations are executed. Consider applying the Sub function, if there are leading spaces. You would never the get the correct output. Essentially, the ALLTRIM function helps to normalize/standardize the raw data fields to allow for accurate transformation.

Syntax: Alltrim(String)

Example in a script to create a new field using Alltrim Function

alltrim-script

DELETE FIELD TEST_DESCR OK
DEFINE FIELD TEST_DESCR COMPUTED AS
Alltrim(ProdDesc)

Example in a workspace to create a new field using Alltrim Function

alltrim-workspace

TEST_DESCR                                 Computed
Alltrim(ProdDesc)

The result from both the above applications of this function would basically return the values from column ‘TEST_DESCR’ without any leading or trailing spaces. There may not be a visible difference in the resulting column as shown in the screenshot below:

alltrim-result

As the screenshot shows, there isn’t any noticeable change in the resulting column. However, it is prepared for any further transformations with accurate results. Now you have seen enough of character functions till this point to be able to use them in your own projects to see a significant impact. See the video demo below:

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




 

ACL Character Function – Sub() Function

Hello to all my fellow number-crunchers. I hope you are finding this series ‘Audit Command Language for Beginners’ useful and interesting. Every step counts on the way to the destination, so lets keep going ahead.

The next character function we will be discussing, is the the Sub function. A very important function for manipulating values from an exiting field. It can be used in a variety of cases ranging from simply creating new fields from existing fields to testing conditions on parts of a field to define a field. Essentially, the goal of this function is to extract a section of a fixed width from string/character based field. The Sub  function can also be written as Substr interchangeably.

Syntax: SUBSTR(string, start, length)

Example in a script to create a new field using Sub Function

sub-function-script

DELETE FIELD TEST_DESCR OK
DEFINE FIELD TEST_DESCR COMPUTED AS
Sub(ProdDesc,1,5)

Example in a workspace to create a new field using Sub Function

sub-function-workspace

TEST_DESCR                    Computed
Sub(ProdDesc,1,5)

Results from either of the above methods would be that the first 5 characters from the column ‘Long_Descr’ will be displayed in the new column ‘Test_Descr’ as shown below in the screenshot.

sub-function-result

This function like most, seems very simple but is extremely useful and powerful for a variety of cases when working with large data sets. This function is commonly used together with other functions for a number of cases. See demonstration of the Sub function.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




ACL Character Function – Replace() Function

Welcome to the latest post in the series Audit command language for Beginners. We are going to dive further into the ACL character functions with the Replace function.

Character functions allow for many opportunities to play around and manipulate with the character fields so as to perform some sort of investigations to better understand the data files. there may be a case where you may want to highlight certain values in the field, in a new field by creating a flag for expected values. Simply use the replace function for the specific string and display a flag value in the new field.

Essentially, the Replace function allows the user to replace a part of the string with the another string of characters.

Syntax: REPLACE(string, old_text, new_text)

Example in a script to create a new field using Replace Function

repalce-function-script

DELETE FIELD TEST_DESCR OK
DEFINE FIELD TEST_DESCR COMPUTED AS
REPLACE(ProdDesc, ‘-‘ , ‘1234567’)

Example in a workspace to create a new field using Replace Function

repalce-function-workspace

TEST_DESCR                                 Computed
REPLACE(ProdDesc,’-‘,’1234567’)

Results from either of the above methods would be that any instance of the character ‘-‘ will be replaced with the value ‘1234567’ when creating the new column ‘Test_Descr’. Please see below screenshot of the resulting column upon the application of the replace function.

repalce-function-result

This function is handy for situations, where one may be required to make changes to the raw data files after discussions with the stakeholders. It happens more often than most professionals would care to admit. Lets face it, there is no such thing as perfect data. Otherwise where would be the fun in this job 🙂 Check it out in action in the video below:

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




ACL Character Function – Include() Function

Welcome back future ACL data analyst. We continue with the series of post for Audit Command Language for beginner data analysts. The next character based function in audit command language is the Include function.

Just like any other function in the Audit Command Language, this is an equally important function. Just like the Exclude function, this function is most often used for the purposes of data cleansing. The difference between the two can be deduced by the names themselves. Nevertheless, to put it in context, there are cases where the requirements are such that only certain characters in the field may be required for a certain analysis. For instance, there may be account numbers in a ledger where alphabets are just added for identification purposes but for overall reconciliations, the actual account number is to be considered. In such cases, only numeric values are required to be ‘included’ in the final formatted data.

Syntax: Include(Data Field or string,<Characters to include>)

Example in a script to create a new field using Include Function

DELETE FIELD NEW_GENDER OK
DEFINE FIELD NEW_GENDER COMPUTED AS
Include(FIELD_1,’M’)

Example in a workspace to create a new field using Exclude Function

NEW_GENDER Computed
Include(FIELD_1,’M’)

The above examples are attempting to include only the character ‘M’ in the gender field in the existing table and create a new field. See the below screenshot for the result.

It really is as easy as it looks. There shouldn’t be any real challenges here for anyone and it is a nifty trick to be able to use to impress your first boss to help him/her with data cleansing. So keep an eye open for any such opportunities.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go to the website to review ACL script examples and ACL commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

 

ACL Character Function – Exclude() Function

Greetings and welcome to the latest post in the series ‘Audit Command Language Tutorial‘ for Beginners. The next few posts are going to be dedicated to character functions as discussed in the last post. Each of the following posts will explore a character function in detail with an example. So let’s dive straight in.

The first of these functions is the Exclude function. A very important function for data cleansing purposes. Every so often there are certain special characters that get included in certain key fields in data sets. In order to ensure correct analysis on these key fields, it is required that these special characters be excluded from the field in order to normalize/standardize the data.

Syntax: Exclude(Data Field or string ,<Characters to exclude>)

Example in a script to create a new field using Exclude Function

DELETE FIELD NEW_GENDER OK
DEFINE FIELD NEW_GENDER COMPUTED AS
Exclude(FIELD_1,’M’)

Example in a workspace to create a new field using Exclude Function

New_GENDER                                Computed
EXCLUDE(FIELD_1,’M’)

Results from either of the above methods would be that any instance of the character ‘M’ will be removed from any relevant values in the column ‘Field_1’ when creating the new column ‘New_Gender’.

This is an easy enough function to use and apply in any project. The only thing to do is to identify the appropriate instance for implementation. Check out the video for a demo of the function :

This post and the subsequent entries should provide for considerable inputs for you to start playing around with data formatting efforts in your own projects.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go to the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.