ACL Character Function ‚Äď Lower() Function

The next two functions in the current series of posts in ‘Audit Command Language tutorials for Beginners’ are quite simple and basic. Its nice to learn some easy things once in a while to keep up the moral ūüôā

So without any further delay, lets jump right into it. The first of these functions is the Lower function. The name of the function gives away its functionality (Its that simple). However, in the spirit of being thorough, this function is used to convert all characters of a string to lower case. It is hard to imagine any practical use of this function except for where conditional statements are being used for an exact match for strings.

Syntax: Lower(String)

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

lower-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Lower(First)

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

lower-workspace

TEST_Field Computed
Lower(First)

Both the above statements return the result where the new column¬†‚ÄėTest_Field‚Äô has the first names all in small case as shown in the screenshot below:

lower-result

The above example illustrates the use of the function. The above example is one of the ways to use this function. Also check out the video below for a demo of the function.

In the next post we’ll cover the UPPER¬†function.¬†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 ‚Äď Upper() Function

Welcome back to the latest newest post in the series ‘Audit Command Language tutorials for Beginners’. This is the second of the easy functions (Lower & Upper). We have discussed the¬† Lower¬†function in another post. In this post we will quickly cover the¬†Upper function before moving onto other complex functions.

So without any further delay, lets jump right into it… The Upper¬†function. The name of the function gives away its functionality (Its that simple). However, in the spirit of being thorough, this function is used to convert all characters of a string to upper¬†case. It is hard to imagine any practical use of this function except for where conditional statements are being used for an exact match for strings¬†and data formatting to normalize the data layout.

Syntax: Upper(String)

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

upper-script

DELETE FIELD TEST_Field1 OK
DEFINE FIELD TEST_Field1 COMPUTED AS
Upper(Test_Field)

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

upper-workspace

TEST_Field1 Computed
Upper(Test_Field1)

Both the above statements return the result where the new columns ‚ÄėTest_Field‚Äô & ‘Test_Field1’ has the first names all in small case and then the same in upper case in the next column as shown in the screenshot below:

upper-result

The above example illustrates the use of the function. There may be scenarios where we may require perform formatting of raw data files to normalize the data. Such situations may warrant the use of Upper & Lower functions. Please see the video for demonstration if 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 ‚Äď 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 – 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.




Audit Command Language – Functions

Welcome to another post in the series ‘Audit Command Language Tutorial¬†for Beginners’. At this point we have discussed two of the key aspects of the ACL tool; creation of scripts and workspaces. The next important piece of the puzzle is ‘Functions’. The remainder of this post is written assuming that the reader has some coding background only to follow along for the terminology.

Functions in any programming language are kind of stored procedures that come with the package to perform certain repetitive tasks. These tasks can be something like, extracting a section of a existing field, formatting data fields to be more presentable way or perhaps just to simply sum up two columns. There are a number of such routine tasks. The functionality of functions provides data analyst the ability to get to relevant data points faster.

Based on the most routinely required functionality, functions are broadly classified in the following three categories:

  1. Character Functions: As the name suggests, this category functions is used on character type data files i.e. imported as ASCII during data import in Audit Command Language software. There is often need for extracting certain sections in a field, or excluding/including/replacing certain characters from a data field and so on. These operations can only be performed on character data types. Some of the most commonly used functions are:
    • Exclude()
    • Include()
    • Replace()
    • Sub() or substring()
    • Alltrim()
    • last()
    • Value()
    • Match()
    • length()
    • string()
    • Split()
    • Upper()
    • Lower()
    • Between()
  2. Numeric Functions: Just like character functions, numeric functions are required to be integers i.e. imported as ‘Numeric’ data type into ACL. Some of the most commonly used numeric functions are:
    • Dec()
    • Round()
    • ABS()
    • INT()
    • Zoned()
    • Between()
  3. Date Functions: Dates are essentially numeric values stored in date formats. Since, the computation on dates are slightly different than regular numbers, there are a set of different functions to extract information from such fields like, current day, current month, day of the week, number of days between two dates and so on. The most regularly used date functions are:
    • CTOD()
    • CDOW()
    • CTOT()
    • DATE()
    • MONTH()
    • AGE()

These basic function types are briefly covered in the following three videos:

Character Functions

Numeric Functions

Date Functions

Besides the above mentioned categories there is another set of functions, leveraged solely for the purpose of data correction in case there are problems in the data file as discussed in the previous posts: data issues ‘split’ & ‘spill’. Most regularly summoned functions for such tasks are:

  1. Recoffset()
  2. Static

The goal of this post is to introduce the readers to functions that would be discussed in the upcoming posts. Each of the above mentioned functions would detailed with its own accompanying post with example and sample scripts that can be leveraged in your own projects.

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.



Audit Command Language – Scripts

Welcome to the latest post in the series ‘Audit Command Language Tutorial¬†for Beginners’. At this point, it is our hope that you are able to successfully navigate the GUI of the ACL tool. If there are still any doubts, go through the video below for a walkthrough of the tool before reading on further.

In this post, we will be discussing how to set up and start scripting in your ACL projects. Audit Command Language scripts are fairly straightforward and easy to write as compared to other tools. In a backhanded fashion, on the simplicity also limits the functionality available in other tools in the market.

Creating a new script: Right click on the project folder in the ‘Project Navigator’ -> ‘New’ -> ‘Script’

script-1

Writing your first lines of script: Try writing the below script to create a new column ‘new_acct’ in the table ‘sample’.

OPEN SAMPLE

DELETE FIELD NEW_Gender OK
DEFINE FIELD NEW_Gender COMPUTED AS
ALLTRIM(Field_1)

After writing the script, press the ‘play’ button. If the script completes successfully, you would see the small green ‘check’ sign in the bottom right section of the tool.

If there is any error, there would a red dot next to the line with the error and a red cross at the bottom right corner of the tool. You may watch the video below for the same walk through.

Of course, there are a number of things that can be done via scripting in any tool and the same is true for Audit Command Language Scripts. Besides the creation of fields, there are functions (character, numeric, date), joins etc. and other commands for various other manipulations that can be performed on data sets. All these different aspects will be covered in separate posts so as to explore each in a detailed and comprehensively.

If you wish to be proactive in learning how to script in ACL, I would urge you to fiddle around with the ACL GUI. Any command that is performed using the GUI is logged in the logs. These can be check as each command is executed in the ‘log’ tab at the bottom of the project navigator. Select the command and the corresponding script would appear ‘Define’ window next to the project navigator. See image below.

These scripts can be used to build simple projects very quickly and allow for practice for beginners. So play around with the tool to learn and practice as much as you can for Audit Command Language Scripts.

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.