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

Welcome again to the series ‘Audit Command Language Tutorial for Beginners’. We have come a long way in understanding many of the character functions in Audit Command Language. Those who have followed along this far, surely are able to take on data transformation tasks with comfort.

This post will add a completely new perspective for your transformation tasks. If you recall, in a video on youtube, we mentioned that when reading in any data files in ACL tool, try to always read every field as a character field. The reason we gave was that all the data must be captured. But there is a problem that arises from that approach. This problem is that numeric fields like amounts and dates cannot be directly manipulated because ACL doesn’t convert the values dynamically. For fields with numeric values, we can use the Value function to convert numeric values read in as characters values back to numeric values.

Syntax: VALUE(string, decimals)

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

value-function-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 Value Function

value-function-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:

value-function-result

This is of course not the best example because you would never be performing any calculations on the ‘Product Number’. However, it demonstrated how to convert string field to numeric values.  Check out the function in action:

Try out all the techniques you’ve learnt on this site and share your thoughts with us.

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.




 

Audit Command Language – ACL Help


Welcome to ‘Audit Command Language Tutorial for Beginners’ data analyst. In this post, we are going to cover an important aspect of learning and using ACL in real time and what kind of resources do you have at your disposal.

In today’s age ACL Audit Command Language has not seen the traction that most other tools seem to have on the internet. There is virtually no discussion thread on any problems related to ACL. even though this tool is still quite extensively used in many organizations, especially firms involved in audit related projects.

Here are the best and most common resources/tips that would help you to successfully complete your ACL Audit Command Language projects and by extension, your analysis.

  1. ACL Help: Most of us, never rely on the built-in help that comes with most tools. In this case, however, it is not really an option. The Audit Command Language help, s the most important resource you can refer to for any doubts. The help is accessible through search on google and in the tool itself. They appear in the same format mostly. The help would cover all topics of course, from functions to scripting. However, it is not very detailed in most cases.
  2. Get hold of sample Script: Assuming you are a beginner to Audit Command Language, it is highly recommended that you get a hold of any templates or past projects from your colleagues to understand coding techniques most commonly used. Make notes of most commonly used scripts used for creating fields, tables, joins, relations, script structures etc. This way, you can start scripting your projects end to end even without the advanced concepts, which can be learnt over time.
  3. Colleagues:  Make friends with colleagues, who have experience in ACL. Experienced ACL folks, just like any other experts tend to be smug but this is rarely preciously knowledge to acquire.

It is our sincere hope that this site becomes another source for concerns regarding ACL Audit Command Language so that we can learn more and help others who are trying to learn.

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 – Data Types

Welcome to the latest post in the series of ‘Audit Command Language Tutorial for Beginners’. Before we discuss the tool in more detail, let us cover a few basic concepts in the next few posts. In this post, let us discuss data types.

There are many data types in the ACL just like any other tool. However, these can be covered in three main categories i.e. character (ASCII), numeric (Float, Numeric) and dates. See the ACL Help.

Also, check out the post on this site for the Audit Command Language Help.

Data types define the nature of the data in any specific column being imported into a table. For instance, a column such as ‘Order_Date’ would have date values, would be imported as ‘DateTime’. See Example

Data Import Screen in ACL

Audit Command Language Data Types
Data Types in Audit Command Language

Similarly, while importing a numeric field, try to ensure that the maximum length is considered to avoid truncated values. Pay special attention to the decimal points for numeric values. Typical values for numeric data types would like ‘Sales’, ‘Units Sold’ etc. Similarly, watch out for columns with descriptive values like ‘Names’, ‘Store Name’, ‘Customer Name’ etc. for character values.

Avoid Data Issues

Very often, users rush to import data into an Audit Command Language project without carefully looking at the raw data. The below tips could be useful for avoiding such issues.

  • Study the raw data files carefully to identify most data types.
  • Make quick notes of the data structure and identify possible relations between data points.
  • All data fields should be imported as character values from the raw data files. These values may be transformed to required formats during the execution of the Audit Command Language scripts.

For those interested in Audit Command Language certification, please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for various certification exams. Also, please sign up for our newsletter to stay up to date with the latest activity on the site.

Audit Command Language – How to set up ACL project


How an Audit Command Language project is set up can improve the efficiency of the overall workflow. It is safe to say that there is definitely no clearly defined way to set up your project, but there are nonetheless a few guidelines you may want to follow. Follow the procedures below:

Set Up Project Folder 

Audit Command Language is quite peculiar in the way it creates its own set of files with their own set of extensions like ‘.fil’, ‘.lix’ etc. It can be quite confusing to have them in the same folder as the raw data files or any other output files that you might create. As general rule, create your project in a directory as shown in the screenshot

folder-structure

In data folder, place your raw data files. The points any files being exported to the ‘Output’ folder. Create your ACL Audit Command Language project in the ACL folder. See screenshot.

Creating a new project in Audit Command Language Software
Open new project in Audit Command Languge

project-set-up-path

Naming the Project

Name the project which best describes your nature of work, with the name including the date. For example, ‘Customer Analytics – Instore Evaludations 01012016 through 06302016′. This format of naming helps to identify the project clearly and is carried over all the way through the log files generated in “Audit Command Language”.

Create a Standard Directory Structure for all Projects

Always create a directory structure dedicated for all Audit Command Language projects. This practice enables users to better manage the project work. This is categorically true for young professionals. Those going through ACL training may want to adopt this best practice as early as possible.

For those interested in Audit Command Language certification, please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for various certification exams. Also, please sign up for our newsletter to stay up to date with the latest activity on the site.

 

Audit Command Language – Introduction


The Audit Command Language is a GUI tool used primarily for data analytics in the field of IT audits and risk assessment. This tool is one of the first tool that most beginner data analytics professionals would come across. This series of  “Audit Command Language Tutorial” is aimed at such young professionals. Listed below are some of the topics, which are a foundation for starting with Audit Command Language. This post covers, the core concepts used in the ACL tool.

Data Import

The first step would be to understand the various methods for importing data into the tool. Audit Command Language is a GUI based tool. This makes it easy to start off with loading your data for your projects. Most commonly used files are delimited files (pipe, comma separated, etc.) with a text qualifier such as double quotes.

  • Delimited files are files, which have data columns splits based on a certain recurring character.
  • Such files may allow for any character to act as the ‘delimiter’ to separate the fields in a data file. It is always preferred if the character is uncommon. For instance, ‘~’ character would be preferred over a comma.
  • It is helpful, if delimited files are exported with text qualifiers for ‘character type’ fields. This mitigates any data issues that may arise due to special characters present in fields like names.
  • Text qualifier covers the entire width of the data column. Please see screenshot below.
Text for input into Audit Command Language tool
Text for input into Audit Command Language tool

Data Types

This is where Audit Command Language, is simpler to most other tools. There are broadly three kinds of data types. These are numeric (amount fields), Text (names, descriptions etc.) and DateTime (dates). The screenshot shows these three data types. The purpose of the audit command language software is to work on risk assessment and audit projects. With this context, these data types serve the purpose. Please note, there are many different data types supported, which would fall under one of these data types. These may be used as appropriate for the raw data being imported into the tool.

Functions

Another extremely important feature for a data analytics software is ‘Functions’. There are predefined functions in Audit Command Language, which allows for data manipulations/cleansing. Audit Command Language Functions are categorized, based on the data types. There are three main categories of functions. These are character, numeric and date functions. There is another type of functions, which is not used very often. These are the Boolean functions. As the name suggests, these are designed to return ‘True” or “False” values.

Joins

All data analytics professionals, would swear on this concept. Joins are the bread and butter for a data analytics professional since this the most commonly used operations. Joins are the combining of two or more data sets based on a common field (or key). Joining data sets is a frequent requirement. Any data analytics professional would find themselves helpless without it. This holds true  in today’s environment, where BIG DATA is a frequent buzz word.

Merge/Append

Just like ‘Joins’, append/merging data sets is an important and regularly required procedure in data analytics projects. This procedure is used to combine data sets vertically i.e. a table with same fields (with same widths and data types) are joined together so the total records are the sum of all the tables being combined. Audit Command language has simple GUI solution to such complex tasks.

Going forward, the above concepts would be discussed along with Audit Command Language scripts and workspaces. This would be the form of instruction on this website to cover each concept. Please refer to reference materials provided alongside the posts. These are helpful study materials to prepare for Audit Command Language certification exams. Please sign up for our newsletter to stay up to date with the latest activity on the site.