ACL Character Function ‚Äď Lower() Function

The next two functions in the current series of posts in ‘Audit Command Language tutorials for Beginners’ are quite simple and basic. Its nice to learn some easy things once in a while to keep up the moral ūüôā

So without any further delay, lets jump right into it. The first of these functions is the Lower function. The name of the function gives away its functionality (Its that simple). However, in the spirit of being thorough, this function is used to convert all characters of a string to lower case. It is hard to imagine any practical use of this function except for where conditional statements are being used for an exact match for strings.

Syntax: Lower(String)

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

lower-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Lower(First)

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

lower-workspace

TEST_Field Computed
Lower(First)

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:

lower-result

The above example illustrates the use of the function. The above example is one of the ways to use this function. Also check out the video below for a demo of the function.

In the next post we’ll cover the UPPER¬†function.¬†Give it try and please share your thoughts and challenges.

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 Character Function ‚Äď Upper() Function

Welcome back to the latest newest post in the series ‘Audit Command Language tutorials for Beginners’. This is the second of the easy functions (Lower & Upper). We have discussed the¬† Lower¬†function in another post. In this post we will quickly cover the¬†Upper function before moving onto other complex functions.

So without any further delay, lets jump right into it… The Upper¬†function. The name of the function gives away its functionality (Its that simple). However, in the spirit of being thorough, this function is used to convert all characters of a string to upper¬†case. It is hard to imagine any practical use of this function except for where conditional statements are being used for an exact match for strings¬†and data formatting to normalize the data layout.

Syntax: Upper(String)

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

upper-script

DELETE FIELD TEST_Field1 OK
DEFINE FIELD TEST_Field1 COMPUTED AS
Upper(Test_Field)

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

upper-workspace

TEST_Field1 Computed
Upper(Test_Field1)

Both the above statements return the result where the new columns ‚ÄėTest_Field‚Äô & ‘Test_Field1’ has the first names all in small case and then the same in upper case in the next column as shown in the screenshot below:

upper-result

The above example illustrates the use of the function. There may be scenarios where we may require perform formatting of raw data files to normalize the data. Such situations may warrant the use of Upper & Lower functions. Please see the video for demonstration if the 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 Character Function ‚Äď Split() Function

The ACL Audit Audit Command Language is somewhat simpler than most other scripting language like SAS or SQL since it allows for a lot of the analysis to be done via  the GUI the tool provides. However, since it is targeted towards certain kind of specific projects, specifically audit related, these compromises are acceptable. However, some of the more complex features of the competing scripting tools.

These features are specific functions that allow for quick formatting and transformation of data for specific complex situations. Some of these functions are ‘Split()‘, ‘Between()‘, ‘Match()‘. In this post of the series ‘Audit Command Language tutorial for Beginners’, we will be discussing the¬†Split()¬†function.

The¬†Split¬†function is used to extract a certain segment from a character function based on a certain character or special character. We will consider the example of a date field to demonstrate the use of this function because it has a special character “/” to split the day, month & year into three clear segments. The function takes as input, the separator and the position to be extracted to give the resulting column with the required segment.

Syntax:SPLIT(string, separator, segment <,text_qualifier>)

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

split-script

DELETE FIELD TEST_Field1 OK
DEFINE FIELD TEST_Field1 COMPUTED AS
SPLIT(TEST_FIELD,”/”,2)

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

split-workspace

TEST_FIELD1 COMPUTED
SPLIT(TEST_FIELD,”/”,2)

The result of the above statements using the Split function is show in the screenshot below:

split-result

In the above example, we are converting a date value into a character value and then applying the¬†Split¬†function in order to extract the month day segment of the date value i.e. number stored as a character in the new column Test_Field1. The above example is a 2-step process to show the implementation of the function. Otherwise, if the date field is imported into ACL as character field, the Split¬†function can be used directly to extract any of the three segments created by the special character “/”. You can see a demonstration of the function 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.

.




ACL Character Function ‚Äď String() Function

Welcome to another post in the series ‘Audit Command Language tutorial for Beginners’. We are moving ahead¬†with a brisk pace with character functions in the Audit Command Language. Hopefully, you are able to keep up and are finding the post useful for your purposes.

In this post, we are going to detail another simple but frequently used function. The String function. This function is used to convert numeric fields to to character fields. This has obvious usage in getting the data formatted correctly to perform transformations on the raw data.

Syntax: String(number, length)

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

string-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
String(Salary,10)

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

string-workspace

TEST_Field Computed
String(Salary,10)

Both the above statements return the result where the numeric values in the column ‚ÄėSalary‚Äô are returned as Character¬†values in the new column ‚ÄėTest_Field‚Äô¬†shown in the screenshot below:

string-result

The above example illustrates the use of the function. The above example is of course an extreme since, you never need to convert a field like Salary to character. However, it demonstrated how to convert numeric field to character values. See a demo of the function in the video below:

Give it try and please share your thoughts and challenges.

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 Character Function ‚Äď Length() Function

Hello again. It would seem at this point that we are making fine progress in learning the Audit Command Language. So far we have covered over 10 character functions. To give a context this is the extent of what you may learn while being in your new job as a fresh data analyst in the first two months (fluently at least). But there is still some way to go in the series ‘Audit Command Language tutorials for Beginners’, so we must keep on pushing through.

In this post we are going to detail the¬†Length function. The goal of the function is pretty self explanatory. It returns the length of the character string in field and returns a new computed field. In most Audit Command Language scripts, this function is used to create a flag of sorts to test whether or not the length of a field is consistent. For instance, typically values in fields like ‘Business Unit’, ‘Account Number’, ‘Customer ID’ are more often than not defined in a scheme which has a fixed length. This function serves as a check to ensure data integrity in such cases.

Syntax: Length(String)

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

length-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Value(ProdNo,2)

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

length-workspace

TEST_Field Computed
Value(ProdNo,2)

Both the above statements return the result where the numbers in the column ‚ÄėProduct Number‚Äô are returned as Numeric values in the new column ‚ÄėTest_Field‚Äô¬†shown in the screenshot below:

length-result

As shown in the above example, we can obtain the length of the field Dept Code and as discussed earlier, we expect that it would be consistent at ‘2’ characters. If the same function would be applied to the field ‘ Dept. Name’ then we would see varied length. We will discuss in a later post how we can use this function as a flag to compute a new field. Please keep practicing and share your experience.You can see a demo of the 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 Character Function ‚Äď Match() Function

Hi again to my fellows data analysts. Allow me to congratulate you on keeping up with the post for ‘Audit Command Language Tutorials for Beginners’. If you’ve made it this far, you’ve come a long way. You have gained some very practical knowledge of how beginner level data analytics is performed in the professional world. To be very honest, ACL Audit Command Language is not targeted towards very high end analytics solutions given that it is mostly used by Audit professionals.

So without any further delay, lets move on with our study of the Character functions in Audit Command Language. In this post we will be covering the¬†Match()¬†function. This is a powerful little function. This function is used to test values in a given data field. Based on whether the test is ‘True’ of ‘False’, other operations can be performed.

Syntax: MATCH(¬†comparison_value, test <,…n>)

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

match-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
“TRUE” IF Match(Upper(alltrim(City)),”LONDON”)
“FALSE”

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

match-workspace

TEST_Field Computed
“TRUE” IF Match(Upper(alltrim(City)),”LONDON”)
“FALSE”

The objective of the above statements in the Audit Command Language Script and Workspace is to create a field based on whether there is a value ‘London’ in the column ‘City’. Please see screenshot below:

match-result

The above example illustrates the use of the function. The above example is one of the ways in how to use the Match¬†function. We can use it for much more than besides just creating a column for flag of ‘True’ and ‘False’. However, the above example provides an insight into the most basic use of this ACL function. In subsequent posts, when we study the conditional statements, this function will take center stage again.

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 Character Function ‚Äď Value() Function

Welcome again to the series ‘Audit Command Language Tutorial for Beginners’. We have come a long way in understanding many of the character functions in Audit Command Language. Those who have followed along this far, surely are able to take on data transformation tasks with comfort.

This post will add a completely new perspective for your transformation tasks. If you recall, in a video on youtube, we mentioned that when reading in any data files in ACL tool, try to always read every field as a character field. The reason we gave was that all the data must be captured. But there is a problem that arises from that approach. This problem is that numeric fields like amounts and dates cannot be directly manipulated because ACL doesn’t convert the values dynamically. For fields with numeric values, we can use the¬†Value¬†function to convert numeric values read in as characters values back to numeric values.

Syntax: VALUE(string, decimals)

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

value-function-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Value(ProdNo,2)

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

value-function-workspace

TEST_Field Computed
Value(ProdNo,2)

Both the above statements return the result where the numbers in the column ‘Product Number’ are returned as Numeric values in the new column ‘Test_Field’¬†shown in the screenshot below:

value-function-result

This is of course not the best example because you would never be performing any calculations on the ‘Product Number’. However, it demonstrated how to convert string field to numeric values. ¬†Check out the function in action:

Try out all the techniques you’ve learnt on this site and share your thoughts with us.

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

Hello all. If you’ve reached this far, please congratulate yourself on your level of commitment to learn a new skill. This attitude will go along way in you becoming a successful professional. So without any further delay lets go further with the series ‘Audit Command Language Tutorials for Beginners’. We are currently at the stage where we are discussing the character functions in ACL Audit Command Language.

In the post we are looking at the¬†Last function. The¬†Last function is a modification of the Sub/Substr¬†function. This function is used in a specific case where it is required to extract a section of a string starting from the end. of course, this can be leveraged in combination with other functions, but still it is a function very helpful in a bind. For example, you may want to extract the value of year from a ‘date’ field. In such a case the last 4 characters most likely would yield the year.

Syntax: LAST(string, length)

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

last-function-script

DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
Last(ProdCls,1)

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

last-function-workspace

TEST_Field Computed
Last(ProdCls,1)

The result from both the above applications of this function would return the last character from column ‚ÄėProduct Class‚Äô into column ‘Test_Field’.¬†The result would be as seen in the screenshot given below:

last-function-result

This function adds another dimension in your approach to transforming raw data files for any project. Such requirement may not be very pressing and there may even be other ways to get the same result. But why struggle when you have a simple method. At this point we expect you would be trying out these functions in your actual projects. Please see the function in action 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.