Familiarizing with different functions

The easiest way to learn what any function does is to play around with it in the ACL command line. You can try now, using the examples from the previous tutorial:

PROPER("john SMITH")
EXCLUDE("VT-123-45", "-")
ALLTRIM(" Chicago ")
BETWEEN(`20170701`, `20170101`, `20171231`)
  1. Open any ACL project in ACL Analytics.
  2. Make sure the command line is open.

    If it is not open, on the main menu, select Window > Command Line.

  3. Copy and paste one of the function examples above into the command line.
  4. Type DISPLAY and a space before the pasted example and press Enter.

    The function output, also known as the return value, appears in the ACL display screen.

    Note

    You can't do anything with the function output in the display screen. It's simply a read-only output that allows you to see what a particular function with a particular input returns.

  5. To temporarily save the function output, pin the display screen.

    Tip

    You can click the linked function in the display screen to quickly reload it into the command line.

Now try changing some input values. . .

Enter or reload the function into the command line, and change one or more input values to see how the output changes.

Tip

If you start doing a lot of experimenting with functions in the command line, you can just type disp instead of DISPLAY.

EXCLUDE( ) example

In the EXCLUDE( ) example, if you add VT to the characters to exclude, you should have output that includes only numbers.

EXCLUDE("VT-123-45", "VT-")

BETWEEN( ) example

In the BETWEEN( ) example, what happens when you change the literal invoice date to 01 July 2016?

The invoice date is the first of the three input values.

BETWEEN(`20160701`, `20170101`, `20171231`)

You should find that the output has now changed from T to F, or from True to False, because 01 July 2016 is not between 01 January and 31 December 2017.

What other functions can I play around with?

You can use the DISPLAY method in the command line to experiment with any ACL function. ACL has over 130 functions, serving a wide range of purposes.

Note

Using DISPLAY with a function in the command line is only for testing or learning purposes. You do not use DISPLAY with functions anywhere else in ACL.

To find other functions to try:

  1. Hover over Functions in the menu at the top of this Help topic, and select a function category.
  2. On the category page, click the name of a function that interests you.
  3. Copy and paste an example from the individual function page into the command line.

    Make sure to choose an example that use literal values, not fields or generic placeholders.

  4. Type DISPLAY and a space before the pasted example and verify that it returns the same output value as shown in the Help topic.
  5. Change input values to create different output values and learn more about how the function works.

    Tip

    Consult the function Help topic if you need help understanding some of the function inputs.

I got an error message

If you get an error message while experimenting with a function, the most likely explanation is that you made a small error when entering the function in the command line.

Some of the error messages may sound serious, but often the error is minor and can be easily fixed if you know what it is.

The rules governing the way functions must be entered, in the command line and elsewhere in ACL, are strict:

Function names Function names must be spelled correctly.
Parentheses

The opening parenthesis must immediately follow the function name with no intervening space:

PROPER("john SMITH") , not PROPER ("john SMITH")

Function parentheses must open and close.

When we come to nested functions, keeping tracking of opening and closing parentheses can be a little more challenging.

Text

Literal text values must be enclosed in "quotation marks".

Quotation marks must be "straight". “Curly or slanted” quotation marks, which can occur when you copy and paste from some sources, cause an error.

Dates Literal date values must be enclosed in `backquotes`, and use a YYYYMMDD format (or YYMMDD).
Fields and numbers

Field names and numbers use no punctuation:

  • Invoice_Date
  • 1000.00
DISPLAY You must preface a function with DISPLAY in the command line (and nowhere else).
Data type

Functions require that input values are a specific data type.

Some functions accept more than one data type, whereas others accept only the Character data type, or the Numeric data type, and so on. The function Help topics tell you what data type or types are valid for each function.

For more information about data types, see ACL data types.

Tip

Minor errors in function syntax can be difficult to spot. Check your syntax carefully if an error recurs.

Function Help topics provide comprehensive information about the required syntax for each function.

Where to next?

Learn how to use functions to filter data in a variety of different ways: Using functions to create filters

[ Back to top ]

(C) ACL Services Ltd. All Rights Reserved. Thursday, October 4, 2018