Audit Command Language – Data Issues

Welcome to the ‘Audit Command Language Tutorial for Beginners’. At this stage, we have discussed how to import delimited files and excel files into ACL projects to perform analytics. The next step is to perform checks to ensure that the data is imported correctly. There are two most common kinds of issues that are seen while importing data into any tool.

  1. Data Spill: This type of data issue causes the data to spill over into the next columns. This generally happens because there may be extra delimiters in some field (generally, this would happen in fields which are descriptions). In the screenshot below, the third line has an extra delimiter.
  2. Data Split: The second most common data issue is the splitting of data lines. This issues causes the data lines to be split across 2 or more lines in the raw file. The same would be reflected in the imported table in ACL. The screenshot below illustrates such an issue. The 6 line in the file has a new line character which is causing the data lines to be shifted to the next line.

Simply having the knowledge of such issues is not enough if these can’t be identified. After all, it is impossible to locate such issues in large files when working on a live project. The fastest way to identify these issues after import are as follows:

  1. Classify Command: In most kinds of data files, certain data columns have a certain set of unique values or similar looking values. For example, a data column like ‘Payment_Mode’ would only have a few possible values like ‘Card’, ‘Cash’ etc. Using the classify command on such a column, should have such values only. If there are any other values, go to the data lines with these values and verify those in the raw data file.
  2. Summarize Command: The summarize command works like the classify command to provide unique values in a column. However, along with the unique values in the column, the summarize command also provides information of the total line counts for each of the unique value and even subtotals for any numeric fields in the data. This information can be further used to locate any problem lines.

You can refer to the ACL Audit Command Language Help for more details on the syntax for these commands. There would be dedicated posts and videos detailing the use of the above statements.

There are some other techniques that you may leverage to identify such issues. There are some helpful tips in the videos below:

Data Spills

Data Split

There are a number of issues that can occur when importing data files. Now that you are aware of the most issues and how to locate the same, you can investigate your data imports for accuracy. This is probably the most tedious exercise, but also the most important, as incorrectly imported data would affect the outcome of the analytics performed.

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 – Print Image Files

Hello again to the series for ‘Audit Command Language Tutorial for Beginners’. For readers, who have been following along the series, should currently be working trying to import data files. So far we have discussed how to import delimited text files & importing excel files. As common as these inputs files are, there is another approach that is extremely useful to learn because life has a way to throw a curve ball when you least expect it. This approach is useful for ‘Print Image’ files. These files are typically in a ‘.pdf’ or ‘.txt’ format where the layout is laid out rather unconventionally yet in a consistent pattern, which can be imported into ACL.

A print image file would look somewhat like this:

import-prn-step-1

The key difference in such a file is that there are no delimiters to separate the columns. Additionally, there is information like the date in the top right corner to be captured along with each line of the detail lines in the data.

Let’s consider an example :

import-prn-step-2

In the above example, there are more data points which form a part of the set of data points, which are to repeated for each of the details lines. These data points appear to be ‘Contact’, ‘Account Number’, ‘Customer’, ‘Order Number’ & ‘Ship Date’.

Now that we can identify the relevant parts of a print image file, lets list down steps to identify the structure of the table that would be created using such a file:

  1. Identifying the Detail Lines – The details lines are the lines are the unique data lines. These are the columns labelled ‘Media’, ‘QTY’, ‘Description’, ‘Label/No.’ etc. All these values are with the exception of column ‘Media’ need not be repeated to fill in a table i.e. the value ‘CD’ for column ‘Media’ needs to repeated for each of the lines below for order number 536118.
  2. Identifying the Header Lines – These are lines which are forming the header section of each block of data. In our example the values for ‘Contact’, ‘Account Number’, ‘Customer’, ‘Order Number’ & ‘Ship Date’ are the header lines. These lines are meant to be repeated across all the individual detail lines for each block. Try and imagine, 4 data lines with ‘Order Number’ as ‘536118’, ‘Contact’ as ‘Marvin Mabry’, ‘Account Number’ as ‘17959’ etc.

A combination of the header and the detail lines would create the complete individual data lines for the final imported table in ACL Audit Command Language. The procedure to import such files is bit more complicated than simple delimited files. The method is covered in the two videos below:

Import Print Image files – Part 1

Import Print Image files – Part 2

It is understandable that when attempting this exercise, you should face some issues. Please leave comments in the videos above on the channel or here on the website. This approach is not just restricted to data import in ACL Audit Command Language. Understanding this concept would allow you to work across different tools. It is imperative that you practice with sample data files. If you need sample files, we can certainly share some. Another good resource for sample files would be a monarch tutorial. It should be available for free online.

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 Import (Excel)

Welcome to the newest post in the series for ‘Audit Command Language Tutorial for Beginners’. As a follow up to the previous post, we are going to explore how to read in the data files provided in an excel format. The concept to import excel files is pretty much the same, but there are a few points that should be kept in mind. This post should do a good job of highlighting said points.

Housekeeping: Before we go through the process of importing an excel file in ACL, lets review a few problem points that should be addressed even before attempting to import and excel file.

  1. Any raw files required for data analytics are best if they haven’t been formatted so that no data is lost. Excel files have a classic problem of ‘self formatting’ data files. Therefore, before importing excel files, it is imperative that all columns are formatted appropriately.
  2. You can choose the formatting options in the ‘Home’ tab in the ribbon. It generally would have default as ‘General’. Select the appropriate format from the drop down.
    • step-1-ribbon
  3. As general guideline, choose format as ‘Number’ with appropriate decimals for any amount fields only. Any other fields, always choose ‘Text’. This avoid any other fields like IDs which start with ‘0’ to keep all characters values.
  4. Once the excel file is appropriately formatted with these two formats, it is ready to be imported.

You can follow the below steps to import the excel files into an ACL project.

  1. Follow the steps to through to the window where you are required to select the type of the source file and choose ‘Excel File’. For earlier steps read post for ACL Data Import – Delimited Files.
    • excle-import-step-1
  2. On the next screen, select the appropriate sheet in the excel file to import data. At this point you may choose define the data types for columns based on first 100 rows if you choose to and allow for first row values to be taken as field names. It is advisable to do so as excel files take longer to import in the ACL.
    • excle-import-step-2
  3. The next screen presents the chance to edit the data types and names of the columns. At this stage, you may ignore any fields. Please note, for excel files, choose the longest possible length for numeric values so that the amounts are truncated.
    • excle-import-step-3
  4. On the next step, save the table in the ACL folder of the project directory and then verify all the columns are appropriately mapped against the expected data types.
    • excle-import-step-4
  5. Save the imported table in the ACL project with the appropriate name and you’re done.

You can look through the video demo for importing excel files below:

Similar steps are followed for MS Access files or SQL tables. However, more often than not, analytics projects receive data in the delimited text or excel files as these are the easiest formats to export data in as well for any firms.

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 Import

Welcome to a new post in the series for ‘Audit Command Language Tutorial for Beginners’. In the next couple of posts, we will be covering various ways to import raw data files into an ACL project. Let’s start with something rather straightforward, i.e. delimited files.

As discussed in a previous post, delimited files are generally in a text format, with characters such as a comma, colon, ‘~’ etc. separating the columns in the data file as below:

You can follow the next steps to import a delimited file into ACL:

  1. Go to ‘File’ -> ‘New’ – > ‘Table’
    • step-1
  2. Select ‘Local’
    • step-2
  3. Select ‘Disk’
    • step-3
  4. Browse sample data file from data folder of your project
    • step-5
  5. On the next screen, select the second option
    • step-6
  6. Choose ‘Delimited Text File’ from the list file formats
    • step-7
  7. The next step, select the following:
    • Set top row as field names
    • enter the delimiter (‘|’ or comma or colon etc.)
    • Select the appropriate text qualifier
    • then choose the appropriate length for each field
  8. Save file in project folder in the ‘ACL’ folder
    • step-9
  9. The next window is the section to edit the field properties:
    • Select the type
    • rename the data field if required
    • You may also choose to ignore the field
  10. Review the fields imported with selected ACL data types in the next screen
  11. Enter the name for the table as appropriate
    • step-12

This step by step guide is generally applicable for almost every type of data file that you might encounter. A couple of the above windows might look different depending on the type of inputs files. For instance, with Excel files, you would encounter a window to select the tab in the sheet, which is to be imported.

It is our sincere hope that this post would help you to get started with your projects. Importing any data file is more than half the battle won when working on data analytics projects in the Audit Command Language.

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 – 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.