Using variables with a function to allow user input

So far, you've been shown how to use fields and literal values as inputs for functions. This approach is relatively straightforward and in many situations in Analytics it's all that you require.

In Analytics scripts, variables are typically used as inputs for functions, rather than fields or literal values.

In this tutorial, we'll look at using variables with a function. The tutorial concludes with a simple script that includes a function with variables as inputs. The variables allow a user to specify the actual input values interactively.

What are variables and how are they useful?

You can think of a variable as a named container in a computer's memory. You provide the name when you create the variable. The variable can temporarily, or more permanently, store whatever value a user selects or specifies.

Variables have two very useful qualities:

  • Flexibility Variables make a script much more flexible.

    For example, instead of requiring a specific field name, or set of dates, a script can use variables to allow users to select or specify whatever field name or dates they want.

  • Clarity Variables make a script easier to understand when you're reviewing it or updating it.

    It's much easier to understand a meaningfully named variable as a function input, than a piece of raw data. You can see the difference with the two examples of the BETWEEN( ) function below.

For more information, see Working with variables in ACLScript.

BETWEEN( ) without variables

Consider the BETWEEN( ) example that we used to create a filter in an earlier tutorial:

BETWEEN(Invoice_Date, `20000101`, `20000331`)

The filter restricts the records in the Invoice_Date field to the first quarter of the year 2000.

This version of the BETWEEN( ) function is fine if we don't mind manually changing the field name, and the start and end dates, every time we want to use it in a different context.

But what if we want to include this filter in a script to be run by other users, against different data, and the other users don't understand how to update the function inputs?

Note, as well, that based solely on a visual inspection, there's no way of knowing conclusively the purpose of the raw data providing function inputs.

BETWEEN( ) with variables

Instead of specifying an actual field and literal date values as inputs for the BETWEEN( ) function, you can specify variables:

BETWEEN(v_date_field, v_start_date, v_end_date)

In conjunction with an interactive script, this version of the BETWEEN( ) function allows a user to pick any date field they want, and specify the two boundary dates.

Note, also, that just by looking at the function the purpose of each input is clear.

Note

By convention, scriptwriters preface their variable names with "v_" so that in a complex script it's easy to see what's a variable and what isn't.

Key point

By using variables, you can create a much broader and more flexible application of a function.

Test the BETWEEN( ) function with variables

You can test the BETWEEN( ) function in the Analytics command line to see exactly how the variables work.

Testing functions in the command line is fully explained in a previous tutorial: Familiarizing with different functions.

Create the variables

  1. In Analytics, at the bottom of the Navigator, click the Variables tab.

    The Variables tab displays all the variables that currently exist in an Analytics project, and the value each variable currently contains.

  2. Create the three example variables by entering the following variable definitions in the command line, one at a time:
    • v_date_field = `20170715`

      For this example, we'll just specify a single literal value for the v_date_field variable, rather than an actual field.

    • v_start_date = `20170701`
    • v_end_date = `20170731`

    In the Variables tab, you should see the three variables you just created, with the assigned values.

Test BETWEEN( )

  1. Copy and paste the BETWEEN( ) example into the command line:

    BETWEEN(v_date_field, v_start_date, v_end_date)

  2. Type DISPLAY and a space before the example, and press Enter.

    The result should be T for True, based on the values contained in the variables:

    15 July 2017 is between the specified start and end dates.

See the result of changing one of the variable values

  1. Update the value in v_start_date by entering the following in the command line:

    v_start_date = `20170716`

    In the Variables tab, you should see the value in v_start_date has updated to 16 July 2017, which falls after the v_date_field value.

  2. Re-run the BETWEEN( ) function in the command line.

    The result should be F for False, based on the values contained in the variables:

    15 July 2017 is not between the specified start and end dates.

Try out the BETWEEN( ) function in a script

The simple script below allows a user to apply a date filter to any Analytics table with a date field.

Don't worry if you can't understand all the script syntax. The main point is to see the BETWEEN( ) function in action in a script.

A COMMENT before each piece of script syntax explains in simple terms what the syntax is doing. Functions are highlighted brown.

How to run the example script in Analytics

  1. Open an Analytics project that contains one or more tables with date fields.

    Sample Project.ACL has several tables with date fields.

  2. Create a new, empty script:
    1. In the Navigator, right-click a folder or the top-level project entry and select New > Script.
    2. Copy and paste the entirety of the script below into the new script in the Script Editor.
    3. Save the project.
  3. Click Run to run the script.
  4. Follow the dialog box prompts to select a table and a date field, and specify start and end dates.

    The script runs and filters the table you selected based on the field and dates you provided.

    Tip

    If you get an empty table, or a large number of records, check the dates in the unfiltered table, and rerun the script with boundary dates that you know will return a small number of records.

Things to note

  • Note that in the filtered table the BETWEEN( ) function appears in the Filter text box with the actual input values you specified.
  • Check the Variables tab. The values in the three example variables are updated with whatever values you selected and specified when you ran the script.

Example script: filter records by date

The example script filters the records in a table by date, using dates that you specify.

Note

You may notice that the CTOD( ) function is nested inside the BETWEEN( ) function. The CTOD( ) function converts character values to date values, which is required in this situation.

If you want to know more, see ACCEPT command.

COMMENT
This simple script allows you to apply a date filter to any Analytics table with a date field.
END

COMMENT Prompts you to select a table in the Analytics project.
ACCEPT "Select a table with a date field:" FIELDS "xf" TO v_table_name

COMMENT Opens the selected table.
OPEN %v_table_name%

COMMENT Prompts you to select a field from the table.
ACCEPT "Select a date field:" FIELDS "D" TO v_date_field

COMMENT Prompts you to specify the start and end dates for the filter.
ACCEPT "Specify a start date (YYYYMMDD):" TO v_start_date, "Specify an end date (YYYYMMDD):" TO v_end_date

COMMENT Applies the filter to the table and field you selected.
SET FILTER TO BETWEEN(%v_date_field%, CTOD(%v_start_date%), CTOD(%v_end_date%))

Where to next?

Review and run a script that uses several functions to help perform a real-world task: Putting it all together: using functions in a script