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.