ACL Character Function – Split() Function

The ACL Audit Audit Command Language is somewhat simpler than most other scripting language like SAS or SQL since it allows for a lot of the analysis to be done via  the GUI the tool provides. However, since it is targeted towards certain kind of specific projects, specifically audit related, these compromises are acceptable. However, some of the more complex features of the competing scripting tools.

These features are specific functions that allow for quick formatting and transformation of data for specific complex situations. Some of these functions are ‘Split()‘, ‘Between()‘, ‘Match()‘. In this post of the series ‘Audit Command Language tutorial for Beginners’, we will be discussing the Split() function.

The Split function is used to extract a certain segment from a character function based on a certain character or special character. We will consider the example of a date field to demonstrate the use of this function because it has a special character “/” to split the day, month & year into three clear segments. The function takes as input, the separator and the position to be extracted to give the resulting column with the required segment.

Syntax:SPLIT(string, separator, segment <,text_qualifier>)

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

split-script

DELETE FIELD TEST_Field1 OK
DEFINE FIELD TEST_Field1 COMPUTED AS
SPLIT(TEST_FIELD,”/”,2)

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

split-workspace

TEST_FIELD1 COMPUTED
SPLIT(TEST_FIELD,”/”,2)

The result of the above statements using the Split function is show in the screenshot below:

split-result

In the above example, we are converting a date value into a character value and then applying the Split function in order to extract the month day segment of the date value i.e. number stored as a character in the new column Test_Field1. The above example is a 2-step process to show the implementation of the function. Otherwise, if the date field is imported into ACL as character field, the Split function can be used directly to extract any of the three segments created by the special character “/”. You can see a demonstration of the function 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 – String() Function

Welcome to another post in the series ‘Audit Command Language tutorial for Beginners’. We are moving ahead with a brisk pace with character functions in the Audit Command Language. Hopefully, you are able to keep up and are finding the post useful for your purposes.

In this post, we are going to detail another simple but frequently used function. The String function. This function is used to convert numeric fields to to character fields. This has obvious usage in getting the data formatted correctly to perform transformations on the raw data.

Syntax: String(number, length)

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

string-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
String(Salary,10)

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

string-workspace

TEST_Field Computed
String(Salary,10)

Both the above statements return the result where the numeric values in the column ‘Salary’ are returned as Character values in the new column ‘Test_Field’ shown in the screenshot below:

string-result

The above example illustrates the use of the function. The above example is of course an extreme since, you never need to convert a field like Salary to character. However, it demonstrated how to convert numeric field to character values. See a demo of the function in the video below:

Give it try and please share your thoughts and challenges.

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 – Length() Function

Hello again. It would seem at this point that we are making fine progress in learning the Audit Command Language. So far we have covered over 10 character functions. To give a context this is the extent of what you may learn while being in your new job as a fresh data analyst in the first two months (fluently at least). But there is still some way to go in the series ‘Audit Command Language tutorials for Beginners’, so we must keep on pushing through.

In this post we are going to detail the Length function. The goal of the function is pretty self explanatory. It returns the length of the character string in field and returns a new computed field. In most Audit Command Language scripts, this function is used to create a flag of sorts to test whether or not the length of a field is consistent. For instance, typically values in fields like ‘Business Unit’, ‘Account Number’, ‘Customer ID’ are more often than not defined in a scheme which has a fixed length. This function serves as a check to ensure data integrity in such cases.

Syntax: Length(String)

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

length-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Value(ProdNo,2)

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

length-workspace

TEST_Field Computed
Value(ProdNo,2)

Both the above statements return the result where the numbers in the column ‘Product Number’ are returned as Numeric values in the new column ‘Test_Field’ shown in the screenshot below:

length-result

As shown in the above example, we can obtain the length of the field Dept Code and as discussed earlier, we expect that it would be consistent at ‘2’ characters. If the same function would be applied to the field ‘ Dept. Name’ then we would see varied length. We will discuss in a later post how we can use this function as a flag to compute a new field. Please keep practicing and share your experience.You can see a demo of the 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 – Match() Function

Hi again to my fellows data analysts. Allow me to congratulate you on keeping up with the post for ‘Audit Command Language Tutorials for Beginners’. If you’ve made it this far, you’ve come a long way. You have gained some very practical knowledge of how beginner level data analytics is performed in the professional world. To be very honest, ACL Audit Command Language is not targeted towards very high end analytics solutions given that it is mostly used by Audit professionals.

So without any further delay, lets move on with our study of the Character functions in Audit Command Language. In this post we will be covering the Match() function. This is a powerful little function. This function is used to test values in a given data field. Based on whether the test is ‘True’ of ‘False’, other operations can be performed.

Syntax: MATCH( comparison_value, test <,…n>)

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

match-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
“TRUE” IF Match(Upper(alltrim(City)),”LONDON”)
“FALSE”

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

match-workspace

TEST_Field Computed
“TRUE” IF Match(Upper(alltrim(City)),”LONDON”)
“FALSE”

The objective of the above statements in the Audit Command Language Script and Workspace is to create a field based on whether there is a value ‘London’ in the column ‘City’. Please see screenshot below:

match-result

The above example illustrates the use of the function. The above example is one of the ways in how to use the Match function. We can use it for much more than besides just creating a column for flag of ‘True’ and ‘False’. However, the above example provides an insight into the most basic use of this ACL function. In subsequent posts, when we study the conditional statements, this function will take center stage again.

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 – Last() Function

Hello all. If you’ve reached this far, please congratulate yourself on your level of commitment to learn a new skill. This attitude will go along way in you becoming a successful professional. So without any further delay lets go further with the series ‘Audit Command Language Tutorials for Beginners’. We are currently at the stage where we are discussing the character functions in ACL Audit Command Language.

In the post we are looking at the Last function. The Last function is a modification of the Sub/Substr function. This function is used in a specific case where it is required to extract a section of a string starting from the end. of course, this can be leveraged in combination with other functions, but still it is a function very helpful in a bind. For example, you may want to extract the value of year from a ‘date’ field. In such a case the last 4 characters most likely would yield the year.

Syntax: LAST(string, length)

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

last-function-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Last(ProdCls,1)

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

last-function-workspace

TEST_Field Computed
Last(ProdCls,1)

The result from both the above applications of this function would return the last character from column ‘Product Class’ into column ‘Test_Field’. The result would be as seen in the screenshot given below:

last-function-result

This function adds another dimension in your approach to transforming raw data files for any project. Such requirement may not be very pressing and there may even be other ways to get the same result. But why struggle when you have a simple method. At this point we expect you would be trying out these functions in your actual projects. Please see the function 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 – 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.