St. Louis ACL User Group

Thank you Nick Panneri & Rene Kennedy from Enterprise for sharing your ACL/SQL experience and expertise with us.

Here is the Enterprise Robust Continuous Audit powerpoint. ACL_UG_Enterprise

We’re planning our next meeting for April 25^th over lunch. More details to follow – but mark your calendars. Billy Cheung who just joined Mercy Health Systems and was previously a consultant with ACL Services will have some great tips for us.

SCRIPTING DATA INTEGRITY VERIFICATION

I like this convenient way to script the data intergity verification step with added value.

1.  Extract the entire record into a file if any field is not verifiable.

OPEN TESTFILE

EXTRACT RECORD IF NOT(VERIFY(field1) AND VERIFY(field2) AND VERIFY(field3) AND VERIFY(field…)) TO “Review_for_Invalid_Data.FIL”

You can send the records to excel if any data validation errors are found.

IF WRITE1> 0 EXTRACT Field1 Field2 Field3 Field4 XLS21 TO “Data_errors”

2.  Then you can also create the normal verify text file to indicate which fields weren’t verifiable.

OPEN TESTFILE

VERIFY ALL ERRORLIMIT 1000 TO “Data_ErrorsDetail.TXT”

Usually,  just looking at the .fil created by step one will be enough.  You can usually see why a record wasn’t verified, but the text file confirms exactly which fields were invalid.  The audience doesn’t have to know ACL to review an excel and txt file of data validation errors either!

Since ACL 9.2 is missing the function reference, we are posting it here.

 
http://docs.acl.com

Select ACL User Guide -> ACL Language Reference -> Functions

Please feel free to contribute any advice or resources for our tips and tricks menu.

Scripting Appends

When scripting appends, you can ensure that duplicate records will not occur even if the script is ran multiple times for the same period or other categorization.

For example, append the following fields to a yearly file in a script intended to be ran monthly:  Month, Location, Average_Lbs.

It would look similar to this:

EXTRACT Month Location Average_Lbs TO “A_Year_Avg_Per_Lb.fil” APPEND OPEN

If this is ran more than once in the same month, the monthly information would appear more than once. Add a summarization with no subtotals to all appends to eliminate this problem.

SUMMARIZE ON Month Location OTHER Average_Lbs TO “Final_A_Year_Avg_Per_Lb.fil” PRESORT

.

Blank Lines in scripts?

Use a blank line under the define line to indicate a conditional computed field.

DEFINE FIELD TEST COMPUTED

(blank line)

“Yes” IF test1 = “A”

“No” IF test1 = “B”

“Bad”

Also use a blank line to end a multiline comment section.

COMMENT

Line 1

Line 2

Line 3

(blank line)

code

Scripting multi-line descriptions

You can easily get multi-lined descriptions by doing it the manual way by right clicking on the column name, going to properties, and changing the alternative column title:

But how do you script this action? Try using the following format:

DEFINE FIELD SaleType2 COMPUTED
AS "Sale;Type"
SUBSTR(ltrim(SaleType),1,1)

Of course, your expression will be different than ours, but the key is the second line where the semi colons are used as line breaks. The result:

Thanks to everyone who joined us for the Panel Discussion.

And a special thanks to our panel!

If you missed it or want a refresher, here is a recap.

James Heavin, Shelter Insurance IS Audit Manager, provided us with a couple of very useful handouts.  Thanks James!

Stay tuned for information on our 4th quarter meeting information.

Calculate Working Days Between Two Days

There may be times when you need to determine the number of working days between two dates. This result can be achieved within ACL by creating a conditional computed field involving multiple ACL functions.

Suppose that start_date and end_date are two date-type fields that are already defined in ACL. Calculating the number of working days between these two dates involves testing for different conditions. For example:

• If the starting date is, for some reason, after the ending date.
• If the starting and ending date both fall on a Saturday or Sunday.
• If either the starting or ending date fall on a Saturday or Sunday.
• If the weekday of the ending date falls before or after the starting date.
• If you need to take into account long weekends or holidays such as Christmas Day, New Year”s Day, and so on.

Read more »

In case you missed the happy hour fun, here are the

handouts.  They include numeric/character

tips, variable tricks, and append hints.

Happy Hour Handouts

Print a table layout cheat sheet

This is convenient when relating and joining multiple tables. If you print the table layouts for each table, you can have the length, type, etc. at your fingertips.

Click on File -> Print Project Contents…

Select the table layouts that you want to print.

You can also print table history, scripts, and view details.