Audit Command Language – Recno Function




Welcome readers, to another post in the ‘Audit Command Language Tutorial for Beginners’ series. Hopefully you have been following along all the posts in the series to get the most benefit out of this website.

In this post we will be discussing the Recno function. This is a very basic and simple function to use in the Audit Command Language scripts. This function is used to provide the current line of each record in a data set.

Syntax: Recno()

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

OPEN EMPMAST
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
recno()

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

TEST_Field Computed
recno()

See below the result of the RECNO function.

As you can see in the above screenshot the  Recno  function allows to create a column with the record numbers. Ths function is especially beneficial when we have to work with multiple data sets and wish to keep track of the original records. Also, if we have record numbers available in data sets, it is easier to back and trace values while designing or troubleshooting errors generated due to incorrect conditions or criteria applied in the Audit Command Language scripts. The use described for this function is a good practice but in many tools, such a function is not always available to implement this easily. The use of this function is demonstrated in the video below:

 

Give it a go and share your experiences. Please look through our various posts for ACL script examples and ACL scripts command. 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 – Classify Command




Hello folks. We hope that you have been following along and more importantly this information has been helpful to you in some way. As always we are trying to add as much content on this site as regularly as possible so that readers can easily follow along and keep up the enthusiasm for learning.

In that spirit, we will keep on going further with another post in the ‘Audit Command Language Tutorial for Beginners’ series. In this post we will be discussing the Classify command. The Classify command is among one of the most frequently used commands in most ACL projects. The Classify command simply displays all the unique values along with the count of total occurrences of each of the unique values. This result is mostly show on the screen and/or it can be printed in the logs as well.

The Classify command clearly bares a lot of similarity to the the summarize command. The major difference between the two is that, when a data set is summarized, we are not sorting the data file and no additional fields besides the key field (the field on which data is classified) and any subtotal fields are allowed. Also, the  Classify command is faster than a summarize if we simply want to extract the unique values because it doesn’t bother with presorting the data, which is a requriement for summarizing any data set.

Let us now explore how to actually use the command. As all things in Audit Command Language, this operation can also be performed using the GUI of the tool and via scripts. to leverage the GUI open the table and simply go to the menu, choose ‘Analyze’ -> ‘Classify…’. A pop window should pop up like shown in the screenshots below:

In the above screenshot, we can select the one field on which we wish to classify the data, the subtotal fields and the any ‘IF’ conditions that we may want to apply in order to filter out the data.

If we move towards the ‘Output’ tab, we get the options to choose the output format i.e. whether the output from the classify command would be provided on screen or in a new table. Depending on the selections that a user would make, we get a small changes to the scripts. For the above set up the Audit Command Language Script would come out as:

OPEN PAYROLL
CLASSIFY ON WorkDept SUBTOTAL Gross_Pay TO “test.FIL” OPEN

For more detailed explanation, we urge you to view the video demonstration of the Classify command. See below:

Since this command is so useful, it is important to understand clearly how it works by playing around the GUI and figuring out changes in output and the scripts created in the logs. This is a terrific way of understanding the Audit Command Language Scripts syntax. Try out this command and share your views in the comments.

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



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.

.