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.




ACL Date Function – Day() 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.

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.  In this post we will be discussing the  Day function. This function is used to extract the day from a date field. This function returns a numeric value.

Syntax: Day(Date)

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

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

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

TEST_Field Computed
Day(HireDate)

See below the result of the Day function.

The Day 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.


Audit Command Language – Extract Statement




Hello again my fellow data analysts. I hope you are having fun. A good sign would be to check if you feel you are making some progress in your professional projects at the work. A sense of progress is a kind of learning. Hoping that is the case, lets continue with ‘Audit Command Language Tutorial for Beginners’.

We will be continuing our discussion on ACL statements. In this post, we will look at the extract statement. The name can be misleading as it may lead some folks to confuse it with the ‘Export’ statement. Unlike the Export statement, which exports the contents of a data set into a new an external application, the extract statement simply extract the contents (as it is or a subset) of one data set into a new or an already existing data set. The sample script below would give a sense of the syntax and application of the statement.

Syntax:

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST

What is this script is doing is it simply opens  the table ‘Payroll’ and extracts its contents into a new table ‘Test’. These two tables would be exactly alike. Let us consider another example where we only extract a subset of the original table.

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST_A if alltrim(WorkDept) = “B01”

In the above script, the new table, would only contain lines from the original ‘Payroll’ table where the field ‘WorkDept’ has the value ‘B01’.

In  another scenario, we may also extract the contents of an existing table into another already existing table. This can be done by using the ‘Append’ keyword at the end of the statement. This would only be possible if the both the tables have the same layout i.e. same number of fields with the same lengths for all fields. Such a statement would be written as:

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST_A if alltrim(WorkDept) = “C01″ APPEND

In the above example we have combined the tables created the in previous example ‘Test_A” with the entries from the original table ‘Payroll’ where the field ‘WorkDept’ has values ‘B01’ & ‘C01’.

The same can be done using the GUI as well. See the screenshots below to achieve the results as per the example above:

For your reference, the above procedure is also detailed in a video. Please see the video demo below:

Try out these examples and keep experimenting. The more you fiddle around the more your learn.

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.