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 Function – Age() Function




Hello guys, we are back. I hope we are feeling proud of ourselves. Give yourself a pat on the back for sticking with the program. Needless to say, we have accomplished quite a bit very quickly. This series of posts, ‘Audit Command Language Tutorial for Beginners’ has turned out to be quite a journey for our team as well. This journey is extremely rewarding for us in ways we did not imagine.

Let us keep on going with the discussion on the next functions in the series of posts for Audit Command Language, date functions.  In this post we will be discussing the  Age function. This function is used to extract the difference between two date values. These values can be provided directly within the function itself or two comparing date fields. This function returns a numeric value.

Syntax: AGE(Date1,DATE2)

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

OPEN EMPMAST
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
AGE(HireDate,`19820101`)

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

TEST_Field Computed
AGE(HireDate,`19820101`)

See below the result of the Age function.

The Age function is easy enough function to implement. It can be seen in the screenshot above how the difference in the date values (provided in the script or workspace) is shown in the new column ‘Test_Field’. It seems most employees are hired after the threshold date ’01/01/1982′ provided in the function. This is indicated by the ‘-‘ sign. Essentially, the calculation is working as ‘Date2 – Date1’. The point to note however, is that this function provides a numeric value. Check out the video demo 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 – Export Statement




Welcome to another post in the ‘Audit Command Language Tutorials for Beginners’ series. We hope all the posts thus far have contributed to you learning the ACL Audit Command Language. At this point you should be able to work on small to medium level data analytics projects comfortably and dazzling your bosses at work. 😉

In the previous posts we have explored various functions which are used for creation of new tools within using Audit Command Language scripts. Going forward, this site will keep adding more posts related functions over time to build a repository for all useful ACL functions. For now, we are going to start exploring some most frequently used commands/statements in the ACL Audit Command Language.

In this post we will discuss the Export Command. It does exactly as it sounds. The command is used whenever we are required to extract the contents of a data set in ACL to an external file. The data can be extracted to any file format, ranging delimited text files to Access files. Needless to say, that commands are only executed via the GUI or via Audit Command Language Scripts. See below for sample script detailing the appropriate syntax.

Syntax: OPEN PAYROLL
EXPORT FIELDS all DELIMITED TO “extract” KEEPTITLE SEPARATOR “|” QUALIFIER NONE

The above script, simply opens the table ‘Payroll’ and then exports all the fields (with header names) into a pipe delimited file.

In order to achieve the same result via the GUI. See screenshots below:

Go to Menu -> Data -> ‘Export to Other Application…’. The below window should pop up.

Be sure to populate and select the values as shown in the screenshot in order to match the sample script provided in the example above. Once you are done, simply click ‘OK’ and check the logs for the executed script. You should get the names of all the fields instead of the ‘All’ we have used in the above example. Both scripts would work just fine.

Now that you are somewhat comfortable using the Wizard for performing the export function, feel free to try out different combinations in the wizard and check out the scripts generated. Keep in mind, that the script would vary slightly for each type of file and the number of the fields (with or without headers) that are to be extracted. For your reference, you may also check out the video detailing the same process as discussed in the post.

 

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 Function – Month() Function

Hi folks, this post is a quick continuation of the last post in the series ‘Audit Command Language Tutorials for Beginners’ where we were discussing the Audit Command Language – Date Functions. In the previous post, we explored the  Year function. Using that function, we were able to extract the year from a date field in a date set.

In this post we will discuss the  Month function. This function, just the Year function, extracts the month from the date field in any data set. This functionality, just like the one provided by the  Year function, allow the basis of more complex data transformations based on these detailed values extracted from the date field.

Syntax: Month(Date)

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

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Month(HireDate)

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

TEST_Field Computed
Month(HireDate)

The below screenshot displays the functionality of the Month function. The new field ‘Test_Field’ is displaying the value of month of the corresponding dates in the column ‘Date Hired’.

This function, along with the Year  provide a way to add additional data points in an existing data set to allow for more detailed analysis if so required. In the next post, we will cover the function  Day, which also complements these functions. Please the video below for Month 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 Date Function – Year() Function




A very warm aloha to all my fellow data analysts. I hope we are feeling very proud of each other. After there is a lot to be proud of. We accomplished quite a bit in the past few posts. This series of posts, ‘Audit Command Language Tutorials for Beginners’ has turned out to be quite a trip for our team as well. In our goal to create the simplest approach to impart this knowledge, we have learnt a few new tricks as well 🙂

In order to keep on improving our approach, let us keep on going with the discussion on the next functions in the series of posts for Audit Command Language, date functions. We have so far covered the Date & CTOD functions. In this post we will be discussing the  Year function. This function is used to extract the year from a date field. This function returns a numeric value.

Syntax: Year(Date)

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

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Year(HireDate)

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

TEST_Field Computed
Year(HireDate)

See below the result of the Year function.

The Year function is a simple enough function to implement. The point to note however, is that this function provides a numeric value. Check out the video demo 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 Date Function – CTOD () Function

Welcome to the latest post in the long running series of ‘Audit Command Language Tutorials for Beginners’. We are making very steady progress with the coverage of topics in these posts. We have so far covered the most frequently used ACL character functions and are currently in the early stages of going through the most common ACL date functions.

In this post, we are going to cover the most commonly used date function. The CTOD  function. The  CTOD function essentially stands for ‘Convert to Date’. This function is used, when the date field in the raw data in imported as a text in a standard date format. in such a case, this function converts the raw field into an ACL date format. There is a list of expected formats that are allowed in the raw data, which can be converted to ACL date format. You can refer to these in the ACL help section.

Syntax: CTOD(string/number <,format>)

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

DELETE FIELD TEST_Field2 OK
DEFINE FIELD TEST_Field2 COMPUTED AS
CTOD(Test_Field,”MM/DD/YYYY”)

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

TEST_Field2 Computed
CTOD(Test_Field,”MM/DD/YYYY”)

The result shown in the screenshot below is a continuation of the example used in the last post, where we converted a Date field to string (‘Date Hired’ to ‘Test_Field’). The same string/text field has been converted back to a date field (Test_field’ to ‘Test_Field2’)  as shown in the screenshot below:

The example used in this post touches upon a couple concepts related to conversion of one data type to another in the Audit Command Language and how they result in creation of new fields in a table. This would give beginner data analysts how more complex scenarios can develop in a real work scenario where data sets are very large. See the video demonstrating the use of the CTOD function 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 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.