Value of Peer Community (formerly the ACL Forum)

Recently there was a discussion on the ACL peer community between an ACL expert user and myself over when certain commands were included in the basic set of ACL commands (e.g. Cross Tab).  I had built a cross tab script before the command existed in ACL, but was wrong on both the date I created the script and when it was included as part of the ACL command set.  This got me to thinking about other scripts that had been written by me or many others that expand the analysis capabilities of ACL.  The functionality of some of these scripts in now included in ACL, but they were ahead of their time when built.  In some cases, the functionality still only exists as a script.

I am sure that many of these script are now in the script hub and strongly encourage you to check out the script hub whenever you have what you think is a unique problem.  It is more than likely that someone has had the problem before; the script was posted on the peer community board; and then included in the script hub.

I wish I could attribute each of the following scripts to the original owners, but my memory is not what it used to be, and often it was a collaborative effort with input/improvements from various users.  I am listing these scripts to highlight the power of ACL scripts and the value of reading the peer community and script hub often.

One_Byte – the first time I saw this approach (more of an approach than a script) was to remove or convert unwanted characters in the middle of a record.  In my case, I wanted to remove CRLF in a description field.  Note:  written before the ability to remove unwanted characters from a delimited file was added to the ACL data definition functionality.

Unzip – uses the ACL EXECUTE command and a VB program to extract zip files (unzip).

Transpose – takes a file where the first column becomes the field names and the other columns become data (rows).

My_Benford – compares the first digit(s) distribution to your “audited” first digit(s) distribution.  Used when your data does not follow Benford’s Law distribution.  Note: this was written before Benford was added as a command.

Create_Workspace – creates a workspace from a file which contains the workspace conditions and values.

Levenschtein_Distance – compares field values for duplicates but allows the values to be close but not exact.  Note: written before ACL added fuzzy duplicates as a command.

Total_Within_X_Days – for example, identifies all credit card numbers where there total charges are more than $10K within any 10 day period.

Wildcard_Search – allows you to search for values and include a wildcard (e.g. D*ve – where “*” can be any character). Note: written before REGEX and REGFIND were added to ACL’s functionality.

Vendor_Clean – cleans the Vendor Name and Address – normalizes the vendor name (e.g. “Corp” and “Corporation”) and the address (e.g. “St” and “Street”).

AlphaNum_Dups – converts a string to alphabetic/numeric order and then searches for duplicates (e.g. “Mr. Smith 126” and “Smith 126 Mr” both become “HIMMRST126”)

QGrams_Dup – looks for duplicates based on grouping of the selected field.

Regression_Anal – perform regression analysis.

Project_info – extracts project items (e.g. scripts, table layouts) from an ACL project to a separate file (e.g. Bat or Fmt file).

Keyword_Search – compares a file with keywords in another file.  This functionality has been developed by numerous users using different approaches based on the size of the files, number of keywords, etc.

Import_All_XLSX – imports all excel files within a physical directory.

Sequential_invoices – looks for sequential invoices from the same vendor (possible fraud).

Invoice_Out_of_Sequence – invoice is not part of a vendors usual invoice sequence (possible fraud).

Make_Dir – creates a physical directory.

These are just a few of the hundreds of scripts that I have built or borrowed from others.  But all of them made their way to the peer community and on to the script hub.  I hope that these examples have shown you that ACL is powerful (even before the ability to run R and Python) and is only limited by your imagination.  I have yet to come across an analysis that ACL could not handle.

This article has 2 Comments

  1. Something like this – using RECOFFSET()
    DEFINE FIELD Dup_Within_X_Days COMPUTED (Co_Code = RECOFFSET(Co_Code,1) AND Invno = RECOFFSET(Invno,1) AND Amount = RECOFFSET(Amount,1) AND ABS(Doc_Dte – RECOFFSET(Doc_Dte,1)) < %v_within_X_Days%) OR (Co_Code = RECOFFSET(Co_Code,-1) AND Invno = RECOFFSET(Invno,-1) AND Amount = RECOFFSET(Amount,-1) AND ABS( Doc_Dte - RECOFFSET(Doc_Dte,-1)) < %v_within_X_Days%)

Leave a Reply

Your email address will not be published. Required fields are marked *