Audit Command Language – Nested Scripts




Hi all, welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. We hope all our readers are able to follow along and able to learn something new with each new post. For all those joining us at this stage, please start at the top of the series to get the most benefit from these posts about Audit Command Language.

In this post we will be discussing an important concept of Nested scripts. This is the closest the Audit Command Language scripts come to the object oriented concept of programming. To be clear has nothing to do with the concept of objects as in C++ or Java. Rather, it is simply the modular approach to programming that is enabled with the nested scripts i.e. different scripts are performing specifically designed tasks so as to keep the program properly arranged.

Lets assume we have a data set is which lists details employee details. We can extract all sorts of information from such a data set. See example of data set below:

Here are a few pieces of information that can be  extracted from this data set. See below:

  1. Extract Country Codes in the data sets.
  2. Extract unique Work dept with total salaries paid across each dept.
  3. All employees hired before or after a certain date.
  4. All employee codes with total salaries paid.

Now all the above information can be extracted by scripting a single Audit Command Language script but it is possible that we may want to perform further analysis on data sets created for each of the above points and it would thus be more suitable to design the scripts individually for each such case. The layout of the scripts might look like as shown in the screenshot below:

All scripts labelled “Analysis” in the screenshot are each individual analysis case for any data set. Once all cases are scripted, the next step is to deign the flow of execution of the scripts. The flow can only be linear in nature in the Audit Command Language. Of course, the same script can be referred to as many times as required for any execution flow. At any stage in any script the flow of the calling other scripts can be done by using the Command ‘DO’. See ACL script example below:

Syntax: Do Analysis1

Simply using the “DO” command allows us to design the flow of any Audit Command Language project. Of course there are endless possibilities of flows that can be explored based on the type of analysis that you are undertaking. We have prepared a demo video showing how such project might look. See the video below:

 

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please look through our various posts for ACL script examples and ACL scripts commands. Please 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 – Recoffset Function




Welcome to another “cool” post in the series “Audit Command Language Tutorial for Beginners”. So far we have discussed various aspects such as functions, command/statements which are used for manipulating data to create new data columns/fields or new tables with additional information. All these functionalities are useful when the data is present in the correct format or straight forward in general. Very often, the data lines provided by clients are not straightforward and needs to be formatted before we can manipulate the data to draw meaningful analysis or conclusions.

For such instances Audit Command Language scripts provide further functionality to address data issues after importing into the ACL views and tables. In the next couple posts, we would be discussing two very important functions that are used to address these very issues. The first of these is the RecOffset function. This function allows the Audit Command Language tool, to read data from lines before or after the current line. Consider the data set in the screenshot below:

Lets say, we are trying to create a new field in this data set. The goal would be to create a new column where the values are taken from the column Dept Code but only from the next line. As shown in the screenshot below:

You can now imagine a case where the data lines are split into two or more rows and you are required to fetch the values of certain columns from the next or two rows down or even above the current data line. This can be accomplished by using the Recoffset Function. This function is used precisely for the reason as described above and shown in the screenshots above. Below is the syntax for this function as it used in Audit Command Language Scripts and Workspaces.

Syntax: RECOFFSET(Dept,1)

ACL Script example to create a new field using RECOFFSET Function

OPEN DEPT
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
RECOFFSET(Dept,1)

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

TEST_Field Computed
TEST_Field Computed
RECOFFSET(Dept,1)

This is a very important functionality provided in the Audit Command Language. It is crucial to understand the use of this function. It can be extremely handy in a tight spot when there is no other way to cleanse the data. The use of this function is also demonstrated 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.




Audit Command Language – Between Function ()




Welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. A lot of important topics have been covered so far in these series of posts. We certainly hope, all our readers are benefiting and learning from these posts. For all those who have just stumbled on this website, we urge you to start at the beginner of this series so as to get full benefit.

In this post we will be discussing the Between function. This function is a logical function i.e. it returns the values as true or false. The Between function is used to test the simple condition of whether or not a certain value lies in a given range. This test could be applied for a numeric, date or a character series. This implies this function works with all three major data types supported by the Audit Command Language.

Syntax: BETWEEN(TEST VALUE ,MIN_RANGE, MAX_RANGE)

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

OPEN EMPMAST
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
“TRUE” IF Between(HireDate,`19820101`,`19830101`)
“FALSE”

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

TEST_Field Computed
“TRUE” IF Between(HireDate,`19820101`,`19830101`)
“FALSE”

See below the result of the BETWEEN function.

Easy enough to use…yes? Just a couple pf things to remember when using this function. The Between function is always used as part of the conditional statement or when filtering out data. There are quite a few scenarios where this function can be useful. For instance, creation of fields, testing values (to create flags) in a column and creating sub sets from existing data sets. Check out the video demo below:

Give it a go and share your experiences. Please 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 – Append




Hi dear readers. Welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. We have been making reasonable progress on this series. We certainly hope all our readers have been finding the posts useful. We have been getting some comments to that effect and it is encouraging to get some feedback from readers. So please keep the input coming and we will try to improve as much and as quickly we can.

In this post we will be discussing an important concept. till now, we have only discussed techniques or concepts with which to manipulate data files within a single data set i.e. creating data fields or adding columns in a view and other things like. However, in a real world project situation, as handy as these basic tools are, we still need to understand how to play around with the whole data sets i.e. combine and multiple data sets and extracting relevant information from multiple data sets into a new data set. Some aspects Audit Command Language scripting are designed to exactly to facilitate these tasks. so with that, we will be covering the Append  statement in this post.

As the name suggests, the Append statement would allow us to append something. But what? how? etc. These should be questions that should come to your mind now. The Append statement allows us to vertically combine to two or more data set in the Audit Command Language. Just like all aspects of the ACL Audit Command Language, this can be achieved in the 2 ways i.e. via GUI or by audit command language scripts.

Before we discuss how to use the Append statement, we must know the requirements for this statement to work correctly. In order to use this statement, the data sets to be combined should be identical wit regards to the the number of fields, the data types of the fields and the fields lengths. If these criteria are not met, the results from this statement would be incorrect. The ACL tool would probably not even provide an error warning. Needless to say, these conditions should be checked before using the append command.

Check out the video demonstrating the Append Statement below:

The video gives a detailed description of how to use the Append statement. Listed below are the ways to understand the scripts used to achieve the same for the readers reference.

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEMP IF RECNO < 10q

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEMP IF RECNO >20 Append

The above scripts are simply creating a new table from the table ‘Payroll’ where the new table should only include the first 9 records and all records after the 20th records in the data set ‘Payroll’.

It is a simple enough statement to implement in your projects. We hope this post was helpful. 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 – Views & Data Sets




Hello people. Welcome to another post in the ‘Audit Command Language Tutorial for Beginners’ series. Without any delay let us discuss an important concept that needs to be understood now that we have covered so many aspects of the tool to manipulate the data files, it becomes important to understand how the tool works with the data.

First let us understand specifically, the data files produced while processing an ACL Audit Command Language project. There are two main aspects to study here. The views and data sets. Let us consider the screenshot below:

When we are looking at the data in the GUI tool, it is essentially the view of the actual data and not the actual data. It is simply a visual representation of the data set. That means, that if any fields are added to a view by using the ‘Add Column’ option, any such fields would not actually be created. In the below screenshot we can see the new field ‘Test_Field’ has been created using the the GUI option. This field is not actually getting added to the data.

This feature can be tested by looking into the actual fields by pressing ‘CTRL + i’ keys. The pop screen lists the actual fields in the data set. In such a view any columns, which are added by using the GUI would not be listed. The screenshot below illustrates this feature.

In order to affect an actual change in the data set with regards to adding new columns/fields, Audit Command Language scripts should be preferably used since in most projects, the creation of new fields should be captured in logs to keep track of the changes to the project. Such changes directly affect an update in the .FIL files that get created for each of the data sets in any ACL Audit Command Language project. Please see a demonstration of the feature described above in the video below:

Such small concepts here and there are very critical in understanding how to work around the tool by using unconventional techniques in the tricky scenario, especially when you are short on time and have to meet urgent deadlines. Knowing such intricate details of the tool is always helpful and this applies for any tool that you may happen to work upon.

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