ACL Date Functions – Date () Function

Welcome to the latest post in the series ‘Audit Command Language Tutorial for Beginners’. We are starting off a series of posts to discuss the most commonly used Date Functions  in the Audit Command Language. In the next six to seven posts we will explore the most commonly used date functions in the professional arena to perform work on industry level projects.

In this post, we start off with the function Date. In the most cases, the raw data files are imported with all fields mapped as ‘text’ or ‘ASCII’ data types in order to ensure that all the data points provided by the clients or stakeholders is captured correctly so that no data points is left out for analysis. ACL functions like Date, Value, String etc. are used to convert the raw data points to required formats during data transformation. The Date function is used to convert data read as ‘Date’ into a string or ASCII format.

Syntax:DATE( <date/datetime> <,format>)

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

DELETE FIELD TEST_FIELD OK
DEFINE FIELD TEST_FIELD COMPUTED AS
DATE(HireDate,”MM/DD/YYYY”)

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

TEST_Field Computed
DATE(HireDate,”MM/DD/YYYY”)

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:

As you can see in the result screenshot, there isn’t a whole lot of change in the way it is displayed. However, if you press “CTRL+i” you can see the data type for the new field ‘Test_Field’ is updated as a computed field. See screenshot below:

The use of this function allows analysts to get more information from the date field to perform additional analysis that my not be possible otherwise. Such transformations always lead to more possibilities. So keep exploring further. Check out the demo for the  Date 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.

 

DATE




ACL – Date Functions

Hello folks. A warm welcome back to the ‘Audit Command Language Tutorial for Beginners’. We have recently completed posts for the most commonly used Audit Command Language String Functions. These functions provide quite an arsenal to be able to format raw data files into useful data points to perform analysis on. We will keep adding more posts to that section as needed in the coming posts. These can be directly accessed through the main navigation bar on this website. Hover on the Audit Command Language link and navigate through all posts for character functions. There will be similar links for other sections in the series ‘Audit Command Language Tutorial for Beginners’.

For now, lets start moving forward with another category of functions in the Audit Command Language. This category is called the date functions. These functions are specifically used for instances to transform or manipulate date values in the raw data. We will be covering the following functions in the upcoming posts.

  1. CTOD( ) function
  2. Date( ) function
  3. Month( ) function
  4. YEAR( ) function
  5. Day( ) function
  6. Age( ) function

A combination of these date functions along with the character/string functions covered up to this gives the users the flexibility to perform much more complex data transformations and data cleansing operations on raw data. Please follow along in the upcoming posts and please let us know if there any specific functions that you readers would like to have discussed.

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.

Audit Command Language – Workspaces

Welcome to ‘Audit Command Language Tutorial for Beginners’ series. Most seasoned analytics professionals often complain that there are better tools that ACL to perform analytics on data sets. In most scenarios, this is a fair complaint against the ACL tool. However, if there is one feature that sets ACL apart from others is the ‘Workspace’. The ‘Workspaces’ in ACL are kind of a short cut to scripting when required to create the new fields. Simply define the required fields in the ‘Workspace’, select the table and activate the ‘Work Space’. You can then simply add the new columns via scripting or the GUI.

Creating a Workspace

Simply right click in the ‘Project Navigator’ on the left of the program. Select New -> Workspace and rename the new Workspace appropriately.

work-space-step-1                   work-space-step-2

Scripting the Workspace

After creating the workspace, you need to write a script for the new column to be created in any existing table. Consider the sample script to create a new field ‘New_Gender’ from an existing field ‘Field_1’. The script in the work space would be

New_Gender                            Computed
alltrim(Field_1)

See below:

Activating the Workspace

Save the script in the workspace. Open the target table, right click on the workspace icon in the Project Navigator and click ‘Activate’. If you receive no pop-up, that implies the script in the workspace is correct and the new field is available to be added in the selected table. See image below

As you can see, the table ‘sample’ is selected the workspace is activated. Next, press ‘CTR + i’ to see the list of fields in the table. The newly created field would be present in that list.

To add a new column to the table using GUI, simply right-click on any column header in the table and select add column. Follow steps in the next windows and you’re done.

Going off the above example, imagine the possibilities of leveraging the same workspace to work on different tables. Essentially, the workspace is acting as complimentary scripts to the main scripts. It is for this reason, that in most script structures in ACL Audit Command Language, fields are not defined in the main body and certainly improves the performance of a project.

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.