In Part I of the Introduction to Scripting series, we introduced you to the ACL Analytics script syntax and the Script Editor, and explained how to create a basic script from the ACL log. We hope you had a chance to try automating some of your work within ACL Analytics by creating basic scripts.
In this edition, we will take a closer look at how to use scripting to create computed fields and how to ensure your scripts are future-proof and readable for yourself and for others.
But first… What is a computed field?
Quite often with data, you will find the fields you need don’t necessarily exist in your source table. So, what do you do? You can create a new field by using the data that is already in the table. This is called a computed field. It is a virtual field that is created from the physical field that already exists to calculate what you need in your new field. There are two types of computed fields:
*Following ACL best practices, all computed field names should be prefixed with c_. This way, you can easily tell whether a field is physical or virtual.
Now that you know what computed fields are, let’s take a look at how to create them using ACL scripting.
Using scripting to create unconditional computed fields
Here is the basic syntax to define unconditional computed fields:
The parameter, field_name, specifies the name of the unconditional computed field, and the parameter, computed_field_logic, specifies how to calculate the value of the unconditional computed field.
Let’s take a look at an example:
This script will instruct ACL to create a new computed field named c_SalesTax and the value of this computed field is calculated by the value in the Rate field multiplying the value in the Price field.
Using scripting to create conditional computed fields
The syntax for a conditional computed field is a little trickier than unconditional computed fields, so let’s tackle it slowly.
Here is the basic syntax to define conditional computed fields:
The parameter, field_name, specifies the name of the conditional computed field. Next, there MUST be a blank line between the DEFINE FIELD command line and the first expression. If there is no blank line, expression1 and condition1 are ignored. The one exception to this? If you want to include optional parameters like field width or an alternate column title, enter them in the second line instead of leaving it blank. The parameter, expressionx, specifies the expression that is applied if the corresponding condition is met. The parameter, conditionx, specifies the condition that must be met in order for the corresponding expression to be applied. Lastly, the parameter, default value, specifies the value to use for records that don’t meet any of the conditions.
Let’s take a look at an example:
In this example, the script reads as: create a new computed field named c_region_name and populate the field with “North America” if the RegionCode = “A00”, or “Europe” if the RegionCode = “BC1”, or “Asia Pacific” if the RegionCode = “E02”. If the RegionCode does not equal to “A00”, “BC1” or “E02”, then put “Unknown” in the field.
If you find the above syntax or example hard to understand, try creating the field manually and then copy the entry from the log into a script.
Making your scripts future-proof and readable
ACL scripts are very powerful because they are automated instructions that you can easily create and modify. But these instructions are a reflection of your analysis needs and as your needs change, your scripts can become outdated or obsolete. In order for the scripts to continue to meet your analysis needs and help automate your analyses, it is necessary to keep your scripts up-to-date. Below are a few techniques you can use in your script to make them future-proof and readable.
Using comments to clarify the behavior of your script
Have you ever reviewed your work immediately after drafting it, only to return later and realize it’s senseless? Same thing can happen with scripting. How do you solve this puzzle? You can use comments to provide context and make your scripts legible. Proving comments on your scripts allows you to describe the high-level logic in layman’s terms.
Here are three common practices of when and how to comment in your scripts:
1. Describe the script, who wrote it, when it was updated, and other important information in a header comment. You should include a script name, a description of the purpose of the script, the author and the date written, and version and date modified, at the very least.
Here’s an example of a header comment that you can use:
This is what you need to know about the header comment syntax:
2. Chunk up larger sections of the script using comment blocks. These comment blocks will make it easier to scan and quickly find logic. Think about comment blocks as ways to identify which script is for importing your data, preparing your data, analyzing your data or reporting on your data.
The comment block example below identifies the script is used for importing data into ACL Analytics.
The comment block syntax has the same rules as the header comments.
3. To increase readability and more importantly, scan-ability, use single line comments to describe more granular logic. This way, when you’re scanning the script, you can clearly pick out the bigger sections and then drill down into the more granular logic.
Here is an example of using single-line comments in a script:
The single-line comment syntax has the same rules as the header comment and comment blocks, except you would include the comment description in the same line as the COMMENT command.
Incorporating variables in order to future-proof your scripts
Consider this scenario: You are analyzing all transactions that took place in 2017 but next year you will be analyzing all 2018 transactions. You can certainly create a script with your year values hard-coded to the year 2017, but you will have to review your entire script next year and adjust every single reference to 2017. Instead of wasting your time reviewing and adjusting, try using variables in your script.
What is a variable? It is a temporary storage location used to hold a value. Variables have an associated identifier that lets you reference and work with the value stored in your computer’s memory. Variables are particularly handy when scripting because they allow you to store results to be used in a subsequent analysis.
How are variables created? There are certain commands in ACL Analytics that will automatically produce variables when they’re run. These are known as system variables. For example, if you run the TOTAL command on a field, the totaled amount that’s generated is assigned to the TOTAL1 variable. Other than the system variables, you can also create your own variables using the ASSIGN command.
*Following ACL best practices, all variables should be prefixed with v_. This way, you can easily identify them within the scripts.
Let’s take a look at this example: you’re analyzing a table and want to extract all sales greater than $1000.
You could script something like this:
But what if your threshold changes later on and you want to identify sales over $2000? Rather than hard-coding the amount into the filter syntax, you can use a variable (v_min_amount) instead.
When the script runs, ACL Analytics reads the value (1000) that’s been assigned to the v_min_amount variable so that the logic reads as the way you have originally scripted:
Watch the video in the “Working with Variables” section of the Introduction to scripting in ACL Analytics course in ACL Academy for a demo of how to create and use variables in your scripts.
Frequently Asked Questions
Here are some Frequently Asked Questions for ACL scripting:
1. How can I add an alternate column title within a script that creates a computed field?
You can use the AS parameter to specify an alternate column title.
Here is the basic syntax for scripting an unconditional computed field that includes an alternate column title:
If you want to specify an alternate column title in your script for defining a conditional computed field, you can simple replace the blank link with AS “Alternate Column Name”:
*Note that the AS parameter can also be used with other ACL commands.
2. I have created a variable in my ACL project yesterday. When I re-open the project today, I can no longer find the variable. Where did it go?
By default, variables are deleted when you close the project. However, you can make them permanent (so that they’re saved even after the project is closed) by prefixing with an underscore _. For example:
- v_min_amount is deleted when you close the project
- _v_min_ amount is saved even after the project is closed
3. I want to use the variable v_table to represent the table I want to open. But when I run the script, I get an error saying “Table v_table invalid”. Why?
ACL Analytics is trying to open a table named v_table, which doesn’t exist. You need to specify that v_table is a variable and not an actual table. So, how do you do this? Simply enclose the variable in % % when referencing it in your script – this tells ACL Analytics ‘This is a variable so don’t try and read it as a name. Read the value that’s been assigned to it.’ This is known as variable substitution.
This is how your script should look like:
Tip of the Month
Consider adding the ‘DELETE FIELD field_name OK’ script before defining a computed field script. Why? A field cannot be created if one with the same name already exists. If you re-run the script without deleting the field, you will get script errors. The ‘DELETE FIELD’ command will delete the field if it exists. If the field doesn’t exist, DELETE doesn’t cause any issues.