Working with variables in ACLScript

When you write a script in Analytics you often need to account for values that change, or vary. For example:

• Script configuration You want an easy way of customizing values in a script's logic, such as table or field names, without needing to update every occurrence of a value

• User input When users run a script, they want to customize input values such as date ranges, or threshold amounts

• Temporary storage The logic in a script needs to temporarily store a value that is continually updated as the script progresses, such as a counter, or a running total

For these, and other purposes, you use variables.

What is a variable?

You can think of a variable as a named container that holds or stores a value that can change. The value can change, but the name of the variable does not change. As the scriptwriter, you use the fixed variable name throughout the script logic. When the script runs, it does not process the variable name. Rather, it accesses and processes whatever value is currently contained by the variable.

In the example below, v_start_date and v_end_date are variable names, and the dates are the values contained by the variables.

The benefit of variables

Throughout a script you can use variables rather than literal values. By replacing actual table names, field names, text values, numbers, and dates with variables that refer to those items, you greatly increase the flexibility of your script. Instead of writing a script that works for only one very specific situation, you can write a script that can be used in an unlimited number of situations.

How and where can variables be used?

Just like the literal items they refer to, variables can be created, renamed, updated, deleted, used in calculations and a variety of expressions, exported, and extracted. In other words, they can be used in any location where the literal item would be valid.

How do I create a variable?

You create a variable with an Analytics command – the ASSIGN command, or one of several other commands. Typically you include the command in a script, but you can also create a variable manually by entering the ASSIGN command in the Analytics command line.

ASSIGN v_start_date = `20210101`
ASSIGN v_end_date = `20210331`

Displaying variables in an Analytics project

As you work with variables, being able to see the variables that exist in an Analytics project, and their current values, is extremely useful. Two methods let you display variables and their values:

• the Variables tab

• the DISPLAY VARIABLES command

The Variables tab

The Variables tab, in the Navigator, tracks the creation of variables and the assignment of values to variables in real time. The read-only tab displays the names, data types, and current values of all variables in an Analytics project. Names are listed alphabetically.

If you step through a script line by line, any variable defined in the script, or any system-generated variable, appears in the Variables tab at the moment of its creation. If the variable already exists, the value updates dynamically, based on the script logic. (In the Script Editor, use the Step option to step through a script.)

Being able to watch exactly what changes are happening with script variables, as they happen, is an important diagnostic tool. This capability allows you to pinpoint script errors that might be hard to locate by examining script syntax alone.

If you run a script, all changes associated with variables are displayed in the Variables tab when a break point is reached, or when the script completes.

The DISPLAY VARIABLES command

The DISPLAY VARIABLES command displays the names, data types, and current values of all variables in an Analytics project. Names are listed in reverse chronological order based on when a variable was created, or most recently updated.

DISPLAY variable_name returns the information associated with a single variable.

You can use the DISPLAY VARIABLES command in two different ways:

• Current state of project variables Type DISPLAY VARIABLES in the command line and press Enter.

  All variables in the project, and their current values, are displayed in reverse chronological order on screen.

• State of project variables at different points in a script Add DISPLAY VARIABLES to a script at the point or points in the script where you want to capture the state of project variables. You can also use a break point for this purpose, with the difference that a break point pauses script execution while the DISPLAY VARIABLES command does not.

  After the script completes, double-click the corresponding DISPLAY VARIABLES entry in the command log. The state of project variables at the point in the script that you specified is displayed in reverse chronological order on screen.

Eight things to know about ACLScript variables

If you are familiar with other programming or scripting languages then you are already familiar with variables and their many uses. The main characteristics of ACLScript variables are described below.

If you are new to scripting and variables, the information below will make more sense once you start working with variables.

1.  A variable is automatically created when you assign a value

2.  Different methods exist for creating a variable

3.  The data type of a variable is automatically specified

4.  Variable names are flexible, with some restrictions

5.  A variable is global in scope

6.  A variable persists until the Analytics project is closed

7.  A variable can be explicitly deleted

8.  System variables are automatically created by some Analytics commands

1.  A variable is automatically created when you assign a value

A variable is automatically created (declared) when you assign an initial value to the named variable. Assigning an initial value to a variable is also known as initializing a variable.

In the example below, the v_input_table variable is automatically created when you use the ASSIGN command to assign the value "Ap_Trans" to the variable. In other words, creating the variable and assigning a value to the variable (declaring and initializing) occur in a single step.

ASSIGN v_input_table = "Ap_Trans"

When and where should you create a variable?

You can create a variable anywhere in a script as long as creation of the variable precedes first use of the variable.

A best practice is to create all user input variables and script configuration variables at the beginning of a script. If you follow this approach, a user can enter all required input values at the start of a script and then leave the script to run. You can also be confident that all required script configuration variables reside in a single, organized location at the beginning of a script.

Variables that do not need user input or advance configuration can be created at the point in a script where they are required.

If necessary, you can assign a blank value to a character or datetime variable so that the variable exists, but is initially empty.

ASSIGN v_input_table = ""
ASSIGN v_all_field_list = BLANKS(20000)
ASSIGN v_start_date = `19000101`

2.  Different methods exist for creating a variable

How you create a variable depends on how you intend to use the variable.

variable_name = variable_value

3.  The data type of a variable is automatically specified

When you use the ASSIGN command, the data type of the value assigned to a variable dictates the data type of the variable. This process is known as implicit typing. In most cases, you do not explicitly specify the data type of variables in Analytics.

The kinds of values you can assign to a variable

You can assign a literal value to a variable, or you can assign a field to a variable and the variable is initialize with the value of the field in the currently selected record. You can also assign the current value of one variable to another variable.

The data types of values

In Analytics, every data value is one of the following data types (or categories):

• Character

• Numeric

• Datetime

• Logical

The data type of a value is established in one of the following ways:

• Literal value The data type of a literal value is established by the presence or absence of qualifiers enclosing the value, and by the alphanumeric characters that make up the value.

• Field The data type of the values in a field are established by the data type of the field.

• Variable The data type of the "from" variable and the value it contains is already set and it becomes the data type of the "to" variable. Typically, you do not change the data types of variables once you have created them, although you are not prevented from doing so.

Examples of using the ASSIGN command to create variables of different data types

ASSIGN v_input_table = "Vendor_number"

ASSIGN v_identifier = Vendor_number

ASSIGN v_min_amount = 1000

ASSIGN v_running_total = Invoice_Amount

ASSIGN v_start_date = `20210101`

ASSIGN v_date = Invoice_Date

ASSIGN v_Q1 = T

ASSIGN v_approved = Approved

4.  Variable names are flexible, with some restrictions

For the most part, you can name a variable in whatever way is most suitable for your purpose.

Voluntary conventions

These voluntary conventions make scripts easier to read and understand:

• Give variables easily understandable, descriptive names that directly relate to the roles the variables perform in a script. A slightly longer, clearly descriptive name is better than a shorter, cryptic name that does not make the purpose of a variable clear.

• Adopt a general naming convention such as prefacing every variable name with v_ .

Imposed restrictions

Analytics imposes these restrictions on variable names:

• Maximum length 31 characters

• Valid characters Alphanumeric characters, and the underscore character ( _ ). The name cannot contain any special characters or spaces, or start with a number.

  Note

  Do not use non-English characters, such as é , in the names of variables that will be used in variable substitution. Variable names that contain non-English characters cause variable substitution to fail.

  Non-English versions of this Help topic, such as the French version, may show variable names with accented characters. Make sure that in an actual script you do not use accented characters in any variable name that will be used in variable substitution.

• Uniqueness Variable names must be unique within an Analytics project because variables function globally across the entire project

Variable names are not case-sensitive. In ACLScript, v_start_date and v_Start_Date are the same variable.

5.  A variable is global in scope

A variable created in an Analytics script is available to all subsequent parts of the script. It is also available to any other scripts in the Analytics project. This behavior is useful if you want to use a variable in a child script that is defined in a parent script.

ACLScript does not support local scoping of variables.

6.  A variable persists until the Analytics project is closed

A variable remains in your computer's memory until the Analytics project containing it is closed, or until you explicitly delete it.

If you want a variable to persist after you close a project, and be available when you reopen the project, you can create a permanent variable. Preface the variable name with an underscore to create a permanent variable. For example: _v_start_date. Both the variable and the value it contains persist. The only way to get rid of a permanent variable is to explicitly delete it.

7.  A variable can be explicitly deleted

To explicitly delete one or all variables in an Analytics project, use the DELETE command in the command line or in a script.

To explicitly delete a particular variable:

DELETE variable_name OK

To explicitly delete all variables:

DELETE ALL OK

What gets deleted?

When you delete a variable you are deleting the named container and the value it contains from your computer's memory only.

Deleting a variable does not affect anything related to the variable in a script. You can reinstate the variable in memory by manually running the ASSIGN command in the command line, or by running the script containing the variable assignment.

Deleting a variable and its value also does not affect anything referred to by the value that has an existence outside the variable, such as an Analytics table.

ASSIGN v_test_table = "Running_totals"

COMMENT Executes successfully
OPEN %v_test_table%

COMMENT Delete the variable
DELETE v_test_table OK

COMMENT Executes successfully
OPEN Running_totals

ASSIGN v_test_table = "Running_totals"

COMMENT Executes successfully
OPEN %v_test_table%

COMMENT Delete the table referred to by the variable
DELETE FORMAT %v_test_table% OK

COMMENT Fails with the error "Table 'Running_totals' invalid"
OPEN %v_test_table%

8.  System variables are automatically created by some Analytics commands

When you execute certain Analytics commands, system variables are automatically created. For example, the COUNT command generates the COUNTn system variable, which contains the number of records tallied. You can use these variables, and the values they contain, in subsequent portions of a script.

For example, this script automatically terminates if the user selects a table that contains no records:

ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table
OPEN %v_input_table%
COUNT
PAUSE "The table you selected contains no records. The script will terminate." IF COUNT1 = 0
ESCAPE IF COUNT1 = 0
DO SCRIPT summarize_transactions

For more information, see System variables created by Analytics commands.

The importance of the data type of a variable

When an Analytics command or function references a variable, the data type of the variable must match the data type accepted by the command or function. If the data types do not match, you get an error and processing of the command or function fails.

To avoid an error in these situations, you can use Analytics functions to convert the stored value of a variable to a usable data type. For example, this expression converts a date stored in a character variable to a datetime data type:

CTOD(v_character_date)

This expression converts a number stored in a character variable to a numeric data type:

VALUE(v_character_number, 2)

For more information, see Conversion functions.

A word about datetime values

In Analytics, you can process datetime values that use either the datetime data type or the character data type. Typically, the data type associated with datetime values is based on the nature of the data when you import it into Analytics.

What is the implication for variables that contain date, time, or datetime values? Simply put, you need to know the data type of the underlying data that you are working with. Your script syntax needs to differ based on which data type is in use.

SET FILTER TO BETWEEN(datetime_date_field, CTOD(v_start_date), CTOD(v_end_date))

SET FILTER TO BETWEEN(character_date_field, v_start_date, v_end_date)

Variable substitution

When you use a character variable as input for an Analytics command or function you need to substitute the value of the variable for the variable name. If you do not perform this substitution, the command or function attempts to operate on the actual variable name, rather than the value contained by the variable, and an error results.

Variable substitution is the method for substituting the value contained in a character variable for the variable name. To use variable substitution, you enclose the name of the character variable in percentage signs ( % ). For example, %v_input_table% retrieves the actual table name stored in v_input_table and substitutes it for the variable name.

Because user input for a script is most often stored in a character variable, a common use for variable substitution is when incorporating user input into script logic.

ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table
OPEN %v_input_table%

ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table
OPEN v_input_table

Using variable substitution can be tricky

Certain situations may look like they call for variable substitution, but in fact variable substitution causes an error.

Consider one of the examples previously presented:

SET FILTER TO BETWEEN(character_date_field, v_start_date, v_end_date)

In this example, you are comparing date values stored as character data in both the variables and the field, so using variable substitution with the variables should work:

SET FILTER TO BETWEEN(character_date_field, %v_start_date%, %v_end_date%)

So why does variable substitution cause an error in this situation? The answer is that variable substitution is absolutely literal. To troubleshoot the error, look at the precise values stored in the variables, manually substitute them into the expression, and look at the resulting expression. For example:

SET FILTER TO BETWEEN(character_date_field, 20000101, 20001231)

This expression compares dates that use a character data type with numbers that post-substitution use a numeric data type, which is a mismatch of data types.

Because you specified a character field with BETWEEN( ), the function automatically interprets the values in v_start_date and v_end_date as character values and there is no need to use variable substitution.

For more information, see Troubleshooting.

An exception to the rule

In some instances, you may be able to use variable substitution with a numeric variable without issue. The benefit is that the value of the numeric variable is captured in the log, which can be helpful for troubleshooting.

Both of the examples below assign the value of the COUNT1 numeric variable to the v_counter numeric variable. However, in the first example, the actual assigned value is not captured in the log. In the second example, the actual value is captured.

COMMENT log entry: ASSIGN v_counter = COUNT1
ASSIGN v_counter = COUNT1

COMMENT log entry: ASSIGN v_counter = 500
ASSIGN v_counter = %COUNT1%

Manually maintaining variables

You can manually maintain variables using the Variables dialog box, available from the Analytics main menu. You can create, rename, duplicate, or delete variables, or update variable values.

Any maintenance you perform affects the current instance of the variable in memory only. For example, if you rename a variable, you are renaming only the instance of the variable in memory. You are not renaming the variable in a script.

1. From the Analytics main menu, select Edit > Variables.

   The Variables dialog box opens and displays a list of all system and user-defined variables in the project. System variables are those created automatically by commands.

2. Do any of the following:

   Create a new variable	Click New to open the Expression Builder. In the Expression text box, enter the value or the expression to assign to the variable. Literal values must use the correct format and qualifiers (when required). For more information, see The data type of a variable is automatically specified. Optional. Click Verify to check that the value or expression is valid. Type the variable name in the Save As text box. For more information, see: Variable names are flexible, with some restrictions A variable persists until the Analytics project is closed Click OK. The variable is created and appears in the Variables tab.
   Update a variable value	Select a variable in the list and click OK to open the Expression Builder. In the Expression text box, update the value or the expression assigned to the variable. Literal values must use the correct format and qualifiers (when required). For more information, see The data type of a variable is automatically specified. Optional. Click Verify to check that the value or expression is valid. Click OK. The value assigned to the variable is updated in your computer's memory.
   Rename a variable	Select a variable in the list and click Rename. In the Rename text box, enter a new name and click OK. The variable is renamed in your computer's memory. Click Cancel to close the Variables dialog box.
   Duplicate a variable	Select a variable in the list and click Duplicate. The variable and its value are duplicated in your computer's memory. A numeric suffix is added to the name of the duplicated variable. Do one of the following: Click Done to close the Variables dialog box. Click OK to update the value or expression assigned to the variable.
   Delete a variable	Select a variable in the list. Optional. Shift+click or Ctrl+click to select multiple variables for deletion. Click Delete. Click Delete again. The variable or variables are deleted from your computer's memory. Click Done to close the Variables dialog box.

Examples of variable use

• Variables for script configuration

• Variables for user input

• Default values for user input variables

• Variables for temporary storage

COMMENT Create and initialize variables for sales tax rates and a date range
COMMENT If required, you can extend the script by adding additional tax rate and date range variables
ASSIGN v_tax_1 = 0.05
ASSIGN v_tax_2 = 0.06
ASSIGN v_tax_2_start = `20210701`
ASSIGN v_tax_2_end = `20211231`

COMMENT Create a conditional computed field that uses different sales tax rates for two different periods
DEFINE FIELD sales_tax COMPUTED

Invoice_Amount * v_tax_2 IF BETWEEN(Invoice_Date, v_tax_2_start, v_tax_2_end)
Invoice_Amount * v_tax_1

OPEN %v_input_table%

COMMENT Create a dialog box to prompt the user for an input table and an output table
ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table, "Specify an output table name (no spaces):" TO v_output_table

COMMENT Open the input table selected by the user
OPEN %v_input_table%

COMMENT Create a dialog box to prompt the user for a numeric field and a numeric range
ACCEPT "Select a numeric field to filter by:" FIELDS "N" TO v_numeric_field, "Specify a minumum amount:" TO v_min_amount, "Specify a maximum amount:" TO v_max_amount

COMMENT Create a dialog box to prompt the user for a date field and a date range
ACCEPT "Select a date field to filter by:" FIELDS "D" TO v_date_field, "Specify a start date (YYYYMMDD):" TO v_start_date, "Specify an end date (YYYYMMDD):" TO v_end_date

COMMENT Filter the input table based on the user's numeric and date ranges
SET FILTER TO BETWEEN(%v_numeric_field%, VALUE(v_min_amount, 2), VALUE(v_max_amount, 2)) AND BETWEEN(%v_date_field%, CTOD(v_start_date), CTOD(v_end_date))

COMMENT Extract the filtered set of records to the output table specified by the user
EXTRACT RECORD TO %v_output_table%

COMMENT Open the output table with the filtered records
OPEN %v_output_table%

COMMENT Set the date format for the duration of the Analytics session
SET DATE "YYYYMMDD"

COMMENT Specify default values for the bottom and top ends of the numeric range
ASSIGN v_min_amount = STRING(5000, 4)
ASSIGN v_max_amount = STRING(100000, 6)

COMMENT Specify the length of the calculated date range in months
ASSIGN v_number_of_months = 12

COMMENT Calculate the start and end dates of the default date range based on the current date and the length specified by v_number_of_months
ASSIGN v_end_date = ALLTRIM(DATE(EOMONTH(TODAY(),-1)))
ASSIGN v_start_date = ALLTRIM(DATE(EOMONTH(CTOD(v_end_date), -v_number_of_months) + 1))

COMMENT Create a dialog box to prompt the user for an input table and an output table
ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table, "Specify an output table name (no spaces):" TO v_output_table

COMMENT Open the input table selected by the user
OPEN %v_input_table%

COMMENT Create a dialog box to prompt the user for a numeric field and a numeric range
ACCEPT "Select a numeric field to filter by:" FIELDS "N" TO v_numeric_field, "Specify a minumum amount:" TO v_min_amount, "Specify a maximum amount:" TO v_max_amount

COMMENT Create a dialog box to prompt the user for a date field and a date range
ACCEPT "Select a date field to filter by:" FIELDS "D" TO v_date_field, "Specify a start date (YYYYMMDD):" TO v_start_date, "Specify an end date (YYYYMMDD):" TO v_end_date

COMMENT Filter the input table based on the user's numeric and date ranges
SET FILTER TO BETWEEN(%v_numeric_field%, VALUE(v_min_amount, 2), VALUE(v_max_amount, 2)) AND BETWEEN(%v_date_field%, CTOD(v_start_date), CTOD(v_end_date))

COMMENT Extract the filtered set of records to the output table specified by the user
EXTRACT RECORD TO %v_output_table%

COMMENT Open the output table with the filtered records
OPEN %v_output_table%

COMMENT
Allow overwriting of files without displaying a confirmation dialog box
END
SET SAFETY OFF

COMMENT
Create a dialog box to prompt the user for an input table and an output table
END
ACCEPT "Select an input table:" FIELDS "xf" TO v_input_table, "Specify an output table name (no spaces):" TO v_output_table
OPEN %v_input_table%

COMMENT
Create a dialog box to prompt the user for a character identifier field
END
ACCEPT "Select a character identifier field:" FIELDS "C" TO v_id_field

COMMENT
Create a dialog box to prompt the user for a numeric field
END
ACCEPT "Select a numeric field:" FIELDS "N" TO v_numeric_field

COMMENT
Index (sort) the table on the character identifier field
END
INDEX ON %v_id_field% TO "ID_field_sorted"
SET INDEX TO "ID_field_sorted"

COMMENT
Assign the current value in the character identifier field to the v_id_value variable.
When v_input_table is first opened, the current value is the first value in the field. Subsequently, the current value depends on the record currently being processed by the script.
END
ASSIGN v_id_value = %v_id_field%

COMMENT
Set the start value for the running total to zero (0.00)
END
ASSIGN v_running_total = 0.00

COMMENT
Process the table record by record. For each unique identifier, calculate a running total for the numeric field.
END
GROUP IF v_id_value = %v_id_field%
  ASSIGN v_running_total = v_running_total + %v_numeric_field%
  EXTRACT %v_id_field% %v_numeric_field% v_running_total AS "%v_numeric_field% running total" TO %v_output_table%
ELSE
  ASSIGN v_id_value = %v_id_field%
  ASSIGN v_running_total = 0.00
  ASSIGN v_running_total = v_running_total + %v_numeric_field%
  EXTRACT %v_id_field% %v_numeric_field% v_running_total AS "%v_numeric_field% running total" TO %v_output_table%
END

COMMENT Open the output table with the calculated running totals
OPEN %v_output_table%

COMMENT Restore the default behavior of displaying a confirmation dialog box before overwriting files
SET SAFETY ON

Troubleshooting

Two general categories of error are the most common when working with variables in scripts:

• Unexpected results The script runs to completion but analysis that includes variables does not produce the expected results.

• Data type issue The script fails with an error related to data type such as "Expression type mismatch" or "Character expression is required".

Unexpected results

For the first kind of error, try this troubleshooting approach:

1. In the Navigator, open the Variables tab.

2. In the Analytics command line enter DELETE ALL OK to delete any existing variables.

   If the Command Line text box is not visible, select Window > Command Line.

3. In the Script Editor, use the Step option to step through the script line by line and track the creation of each variable and assigned value in the Variables tab.

   You may discover that the value being assigned to a variable is not what you intended and your script logic needs to be adjusted. For more information, see Testing and debugging scripts.

Data type issue

As you develop scripts that make use of variables you will likely encounter errors caused by mismatched data types. Errors are especially likely if you are combining conversion functions and variable substitution in the same expression.

Encountering errors is normal, and the way to overcome them is to test different approaches and to keep these principles in mind:

• Pay very close attention to the data types in every element in an expression. Are all the data types in an expression aligned in the way that ACLScript requires?

• Remember that variable substitution is absolutely literal. If you suspect that variable substitution is causing an error, look at the precise value stored in a variable and manually substitute the value into the expression. Just performing this simple troubleshooting step may reveal the problem.

• Inserting the DISPLAY VARIABLES command at different locations in a script allows you to capture the point-in-time state of project variables.

• Stepping through a script line by line, and setting break points, can make troubleshooting much easier. For more information, see Testing and debugging scripts.