Advanced ACL tips and techniques.

An advanced class for users of ACL with 20 or more hours of ACL practical experience that want to:

• enhance their knowledge of, the types of tasks they can perform with acl
• increase their efficiency
• learn about advanced features of ACL.

Description

This course builds on the knowledge and skills taught in the introductory level of data analytics training. Participants learn new ways to work with the basic features of ACL and are introduced to new tricks and techniques that enhance the ability to produce results. This course focuses on advanced techniques for ad-hoc, user-operated analysis. All the concepts are transmitted through a series of practical case studies designed to maximize the practical learning environment.

Prerequisite

As this course covers advanced topics, participants must have:

§      An understanding of the commands covered in the introductory level of training.

§      Worked with ACL for a minimum of 20 hours performing various tasks OR have the advanced tips and techniques combined with an introduction course.

Contents

1- Efficient methodology for doing ACL projects

It will start with a very simple case study. The main focus of this activity is the methodology of doing a complete ACL project.

Methodology outline:

Concepts reviewed or learned in this simple case study:

·         Copy a table layout from another project

·         Define only the needed fields of a flat file

·         Extensive use of User Defined Variables

·         Introduction to the command line window

·         Introduction to some functions to harmonize key fields

·         Combining commands to obtain the desired result

·         Review of joins

·         Prepare a complex conditional computed field describing the status of each line

·         Use Workspace to be able to use this field in similar situations.

·         Prepare formatted report with break key fields.

·         Prepare summary information that will be exported from the log to an HTML output.

2- Advanced Data Quality checks

Frequently, an elaborate initial advanced data quality check prevents investing time on data that eventually proves to be inappropriate for the intended queries.  It often highlights immediately additional parameters that could refine the planned tests.

Key Text fields: (12+ basic tests)

Key Numeric Fields: (5+ basic tests)

·         Check repeated occurrences of some values by using String() or zoned()

Key Date Fields: (5+ basic tests)

3- Advanced data file definition

  3.1 Report files

Use a variety of report files that will cover all the main options and situations that can be handled without any programming.

  3.2 Delimited files

3.2.1 Techniques to check the integrity of the delimited file

3.2.2 Regular and frequently received delimited files

3.2.3 Files that have to be defined manually

3.2.4 Corrupt files with records broken down by invalid characters

  3.3 Excel Files

The Excel files are ubiquitous. Some Excel files are what we could call false Excel files. They are report files with headers and footers that have been sent in an Excel format. All CSV files should rather be defined as delimited files rather than Excel. In the regular Excel files, (titles on first row, no blank data column or rows) at times some fields that alternate between numeric and alpha numeric data can cause some problem.

3.3.1 False Excel files

You receive these files as an Excel document. However it looks more like a report file with headers and subtotals

3.3.2 Alpha Numeric Problem

  3.4 Some log files with no carriage returns.

  3.5 The data with electronic table layouts

When relevant for the group, we will give examples of the following types of data. In this case the participants will be encouraged to bring an example of their data in class.

·         As 400 data with the File Definition Format file

·         Cobol data file with the Copybook format file

·         The SAP private file format.

4- Advanced duplicates searches

There are some common basic patterns in the various types of duplicate searches that are commonly used in the Fraud detection and Compliance environment.  Some of these fundamental duplicate searches are also the basis for the technique used to look for split purchases to avoid the threshold amount where bids and control take effect.

Theses tests can be done at the requisition, purchase order, invoice, payment level or any other situation where the same logic can be applied (ex: inventory).

4.1 Duplicates of a combination of key fields

When some combination of fields such as Vendor, Invoice, Part Number and Dates are repeated across different records.

There are variations on that theme:

4.2 Same Same Different scenario

When a combination of key fields is associated with a different third or fourth field. .

5- Cross referencing data from various sources:

There are often incompatible sources of information within a company, branches of a company, legacy systems from acquired companies, other outside sources of information.

In ACL we can use an efficient set of functions to harmonize the data from disparate sources in order to do an analysis referencing all the needed sources of information.

6- Most of the content of the functions course

The functions course is a one day course that an autonomous course. However it complements nicely this course and most of it is usually included in this curriculum.