Category Archives: ACL: Beginner’s Corner

ACL Crash: How to Decide Between Working Copy and Last Saved

An ACL project crashing can be a disappointing moment for any ACL user. Here is a summary of what you will see and some helpful tips to take into consideration when determining which option will be best in your situation. We’ll also show you how to make a copy so you can keep both versions in case you accidentally make the wrong choice.

The Options

When you go to reopen the project you are left with two options: Working Copy or Last-Saved version.

ACL-usergroups-stlouis

The working copy is a .AC file that is automatically created by ACL for every project and serves as a temporary auto-save file. All unsaved changes to the ACL project are recorded here so that the changes can be recovered if ACL closes unexpectedly.

The last-saved version is the .ACL file, and contains the project at the point in time that you last saved.

WARNING!!! By selecting the working copy, ACL will automatically overwrite the last saved version as soon as it is opened.

Deciding Factors

1-     How large is the project file?

2-     When was the file last modified?

3-     What were you doing?

First, review the file size and last modified date/time. To do this, find where your ACL project is located on your computer/network drive. Sort your folder by Type (so the .AC and .ACL files appear at the top) and compare the file size and modified date/time between the .ACL file (last-saved) and the .AC file (working copy).

June 2

  • Factor 1- File Size

Usually, a larger file size indicates that there is more content in the project. However, the data may be bad if it didn’t complete the analysis/command that ACL was in the middle of processing when it crashed. Depending on what you were doing (see below), it will not always be obvious that the analysis/command was mid-process when the project crashed. In addition, ACL frequently creates large temporary files to execute a command. These files will impact the size of the project, but may not be critical to your results. Temporary files may be misleading if you are relying on the file size to determine which option to select.

  • Factor 2- Last Modified Date/Time

If you know the last time/date you added or modified something that you need, you may want to consider this factor more heavily than the project size when choosing which file to open.

  • Factor 3- What was ACL Doing?

After comparing the file size and last modified date, determine what you were in the middle of doing when ACL crashed. Knowing what ACL was doing when it crashed is likely to be the most critical factor to determining which option to pick.

A few examples of things that you may have been doing are listed below. If none of the options below describe what you were doing, skip ahead to the “When You Don’t Know” selection below.

Importing Data– The larger size file likely has more data, but may be incomplete… you may want to consider the modified time and go with the option that you know is correct/complete.

Performing Analysis with a Command-  This is a tough call. You may want to default to the “When you don’t know” selection below just to be safe.

Writing a Script-  Scripts typically do not significantly impact the file size. The most recent file may be your best bet.

Running an Analysis Script-  This is also a tough call. You may want to default to the “When You Don’t Know” selection below just to be safe.

Running a Clean-Up Script-  The smaller size file is likely the most up to date.

When You Don’t Know- Play it Safe

If you are not 100% sure which one to choose, you can always select cancel and create a copy of both files and save it as a different name. If you discover that the selected copy is not what you want, simply close the project and open the re-named copy that you created. If necessary, you can change the file extension from .AC to .ACL to open your re-named copy.

Using the ACL Command Line

The command line can be a very helpful tool to detect the cause of an error in a script, quickly see results, open a table that may be hard to find in the project navigator folders, or to quickly display the table layout or history. Below we have summarized the steps to use the command line for these procedures, and how to change your display settings to view the command line.

View Command Line

If the Command Line text box is not visible, select Window from the ACL menu bar and select Show Command Line.

May 1

Execute Commands Individually to Find Script Error

To edit and/or re-execute commands recorded in the log file. While you can only enter one command at a time, stepping through the code line by line can be helpful in working backwards and detecting where an issue occurred. This can be done by clicking an entry in the Log, adding it to the command line, and clicking the green play button on the right to run the command.

May 2

May 3

Open a Table

  • To easily find a table that you would like to open in your project, go to the log and click on one of the OPEN commands for the table you would like to open. The command will now be displayed in the command line and will open after running the command.

May 4

  • After running this command in the command line, there is also the option to use the F4 key to get a list of tables in your project.

May 5

Quickly See Results

To quickly evaluate the effects of an expression with the CALCULATE command.

May 6

Quickly View Table Layout or History

  • To quickly view or print the table layout in ACL, type DISPLAY or DIS in the command line. The table layout has useful information about the fields in a table, such as the length of a field, the field type, and the expressions used to create computed fields.

May 10

  • To quickly view the table history in ACL, type DISPLAY HISTORY or DIS HIS in the command line. This command will display the history of the current table you have open.

May 11

Always a Character!

Working in public accounting I am very aware that busy season is in full swing. With that comes journal entry testing! What is journal entry testing? Basically, our team provides the audit department with support in identifying journal entries that could be tested as part of their audit program. Don’t worry, I’m not diving into that in this post. The key point is that a lot of the general ledgers we receive are — how should I put this — less than ideal for imports.

How do we get around iffy fields? Simple, import everything as a character field (ASCII to be exact) and define later. Debits and credits? Character. Dates? Character. You get the drift. Not only does this help prevent issues, it will also help you find errors.

I recently ran into a problem where the description field was pouring into the debit field. The company had ended a few descriptions with “2013”. When I looked at the imported character field, it showed “20131,000”. Had I imported this as a number, I only would have seen a $20,130,xxx debit, not the weird punctuation that alerted me to the issue.

Bottom line, import as character, define your own fields, and enjoy easier analysis!

Many to what?

As I was learning ACL, a difficult area for me was joins. I spent many hours visualizing what records would end up where. Not to mention, how to decide which table is primary? Today I’ll attempt to explain one type of join that has helped me tremendously: a matched primary join with all primary and secondary records present.

Let’s start with some vocabulary:

Primary and Secondary – Hopefully this is pretty straight forward…

Key field – This is the field ACL will use to match my two records together. Some examples are employee numbers, check numbers, last names, and cities. Both of my primary and secondary tables will have these, preferably the same length.

Many-to-one – ACL will default to matching the first record in the secondary table to my primary key. Personally, this was confusing. I chose to ignore it when I was learning joins, and you should just know that it can pose a problem.

Diving right in, my goal is to create a join that is easy to understand and quick. How am I going to do this? Continue reading

Beginner’s Corner: Planning

One of the most helpful tools I have found while learning ACL has been pre-writing my actions. What do I mean by pre-writing? I attempt to describe in English what I want ACL to do and what I expect the output to be. Are five records reasonable or 500? Should the computed field have 12 digits or only three? This has caught me off guard more times that I care to admit.

Let’s walk through an example from one of the tenants of ACL, data integrity verification. Consider a file where the client has provided us check information, invoice information, and PO number:

ACL_Data_Example

 

The client’s system is a bit crazy and randomly duplicates invoices. The problem is we know that it will duplicate the invoices without filling the PO number information. We can see this on row 5 and row 7. Row 10 is a legitimate blank, but we need to get ACL to tell us that.

In my head, here’s what I’m thinking,

“I need ACL to consolidate these records to remove the false duplicates. I want to summarize on the vendor (Paid_To) and invoice number (Invoice_No) and have all fields still available to me. Then I will search for blanks on the PO field. I expect one blank to show up (check 10006/row 10).”

I would then convert that paragraph to ACL:

  1. Summarize on Paid_To and Invoice_Date, with all other fields in the OTHER box.
  2. Set filter to ISBLANK(PO_No).
  3. Extract to final table.

In ACL scripting (if you aren’t familiar, don’t worry):

ACL_Code_Ex1

All this gives us the following table as output:

ACL_Final

If you’re not familiar with the summarize command or scripting, don’t fret. My goal was to equip you with the skill of defining your expectation and process. Now when you run through the ACL tutorials and language guide, you can spend some time planning. I’d also suggest peeking at the log every so often. ACL writes your code for you, so it makes scripting very easy (which I’ll dive into in a few weeks).

New Series!

We are introducing a new series for the blog today, aptly called “ACL: Beginner’s Corner.” The goal is to offer advice from someone learning ACL in the trenches, sharing shortcuts, processes, and ideas that decrease the learning curve for new users.

Who will be writing this series? Good question. Let me introduce myself. My name is Rob and I am part of the Advisory Services practice at Brown Smith Wallace. If you want to see my forced smile and learn all about me professionally, check out my LinkedIn page here. I recently moved from our Audit practice to Advisory Services to join the Data Analysis Group. Jan Beckmann leads the group and is quite known for her ACL expertise (check her out here and here) No pressure, right?

My ACL journey started back in July and I plan to touch on subjects that range from specific ACL actions to big picture thinking about the data. My way isn’t the only way, and may not be the best way, but it will give a new ACL user an idea as to what I was thinking.

As always feel free to comment and share any of your own tips or tricks!