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.


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.