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:

  • Unconditional – the same expression applies to every record of the physical field.
  • Conditional – different expressions apply to the physical fields based on each condition.

*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:

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:

Define unconditional computed fields 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:

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:

Define conditional computed fields 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:

Header comment example

This is what you need to know about the header comment syntax:

  • COMMENT command is the first line
  • Don’t include anything else on that first COMMENT line
  • Skip a line to end the comment block
  • Use * and spaces to increase the legibility of your scripts
  • Make sure all your comments are green – if not, something’s wrong with your 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.

Comment block example

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:

Single line comment example

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:

Incorporating variables example

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.

Using a variable for filtering

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:

Using a variable for filtering - script has run

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.