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




Audit Command Language – Sort Statement




Welcome to another post in the ‘Audit Command Language Tutorial for Beginners’. We have covered a lot of ground in just the last few weeks. So far we have covered a range of functions and statements which should be enough for entry level data analytics professionals. In that spirit let’s keep building this knowledge base.

In this post we will be discussing the Sort statement. It is quite obvious as to what this statement is used. This statement is used to sort a data set. However, there is a surely a ACL specific twist related to this simple task.

First lets see how we use this command via the ACL GUI. In order to use the GUI to implement the command go to menu -> Data -> Sort Records… . This should result in a pop up window. It should appear as shown below in the screen shots below:

At this stage, click on ‘Sort on’ button to select the field on which to sort the table.

At this you can choose the field by moving it to right column. At this point, you can further click on the arrow icon to sort data ascending or descending.

Once you set up the pop up window as shown, you should be able to get the new sorted table ‘Test’. The syntax would appear as follows:

OPEN PAYROLL
SORT ON Gross_Pay TO “Test” OPEN

In order to sort the data descending, the script would read as follows:

OPEN PAYROLL
SORT ON Gross_Pay D TO “Test” OPEN

To further aid your understanding of this statement, please refer to the demo video shown below:

This statement is a simple enough to implement. It can be argued that it is not very useful for investigations or advanced analysis, but it must be noted that sorting data is generally an intermediate step to other major operations like Summarizing data or joining data.

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 – Summarize Statement




Welcome to another post in the ‘Audit Command Language Tutorial for Beginners’ series. We hope things are moving along nicely for all our readers. It has indeed been a long enough journey up to this point and it is certainly our hope that it has been fruitful so far.

We are going to continue with the posts exploring the Audit Command Language statements. In this post, we will be discussion the ‘Summarize’ statement.

The summarize statement performs a rather simple task but one which has quite an impact in any and all data analytics projects. Lets put it this way, if you haven’t found any major insights into your analytics projects, it simply means that the summarize statement has probably not come into play yet.

Before we discuss some of the most common uses of the statement, let us quickly go through the application portion i.e. syntax and GUI implementation.

Syntax:

OPEN PAYROLL
SUMMARIZE ON WorkDept SUBTOTAL Gross_Pay Net_Pay Tax_Amount OTHER Cheque_No TO “summ_test.FIL” OPEN PRESORT

It is important to understand what this statement is doing to get a grasp of the syntax. Firstly, it opens the table ‘Payroll’. Then the field on which the data set is to be summarized, will be provided. Here we are summarizing the data set on field ‘WorkDept’ i.e. the resulting table or view would only have the unique values present in the field ‘WorkDept’. The next part of the statement is listing fields (specifically numeric fields) to be totaled for each of the ‘WorkDept’ values in the data set. the PRESORT at the end of the statment indicates that the ‘Payroll’ data set is sort prior to the use of the statement. Please note, it is critical that a any data set be presorted before summarizing the same, else the results would be incorrect.

To summarize the resulting data set created from the above example would be displaying the total ‘Gross_Pay’, ‘Net_Pay’ & ‘Tax_Amount’ across each of the unique ‘WorkDept’ in the ‘Payroll’ data set.

To accomplish the same using the ACL GUI, please refer to screenshots below:

Go to Menu -> Analyze -> Summarize. Thereafter you should get a pop  up window. Update it as shown in the screenshots:

Please feel free to play around with the interface for this statement. It has a variety of options, all of which cannot be explored in just one post. We can create another post more specific to any specific queries that we may receive.

Additionally, you may prefer to the videos provided below for a demonstration of the summarize statement:
Part 1

Part 2

The above videos should provide even more clarity about the Summarize statement. This statement is a powerful statement because it provides the unique values in a specified column in a data set. Since this statement provides this specific information, it allows for numerous possibilities when combined joins and merges in the Audit Command Language tool overall. The scope of the analysis that can be undertaken simply by including this statement in your arsenal has more impact than all the functions combined. Not to get too dramatic about it, but once you grasp this concept well, you can pick it up very quickly in any scripting language very quickly and start being super productive from the start.

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