ACL assignment

ACL_in_Practice.pdf

Home >Business & Finance homework help >Accounting homework help >ACL assignment

ACL IN PRACTICE

No part of these materials may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means (photo- copying, electronic, mechanical, recording, or otherwise), without permission in writing from the publisher, except by a reviewer who may quote brief passages in a review.

These materials may not contain all the information, or the most current information relevant to your situation or intended appli- cation. Support and update services are available from ACL, separately.

ACL 9

August 2006

ACL Services Ltd. 1550 Alberni Street Vancouver, BC Canada V6G 1A5

Telephone: +1-604-669-4225 E-mail: [email protected] Web: www.acl.com

Printed in Canada

ACL, ACL Data you can trust. Results you can see, the ACL logo and Audit Command Language are trademarks or registered trademarks of ACL Services Ltd.

Microsoft, Windows and Windows Server are trademarks or registered trademarks of Microsoft Corporation. AIX, OS/390, OS/400 and z/OS are registered trademarks of IBM Corporation. Linux is a registered trademark of Linus Torvalds. SAP, R/2 and R/3 are trademarks or registered trademarks of SAP AG. Crystal Reports is a trademark or registered trademark of Business Objects SA. All other trademarks are the property of their respective owners.

200607051342

Tutorial.book Page ii Wednesday, July 5, 2006 1:42 PM

CONTENTS . . . . . . . . . . . . . . . . . . . . . . . . 1 ............................................. 2 ............................................. 3 ............................................. 4 ............................................. 5 ............................................. 5 ............................................. 6 ............................................. 6 ............................................. 7 ............................................. 8

. . . . . . . . . . . . . . . . . . . . . . . . 9 ........................................... 10 ........................................... 10 ........................................... 11 ........................................... 13 ........................................... 14 ........................................... 15 ........................................... 15

Tutorial

Chapter 1: Introducing Metaphor Corporation . . . . . . . . . . . Tutorial objectives ................................................................. About Metaphor Corporation ...............................................

Metaphor credit card policies ........................................ Acceptable category codes ............................................. Metaphor data files .........................................................

Plan your analysis .................................................................. Preliminary investigation ............................................... General tips to consider ..................................................

Chapter review .......................................................................

Chapter 2: Examine employee data . . . . . . . . . . . . . . . . . . . . . . Examine the employee list .....................................................

Open and view the employee data project ..................... Get a statistical picture ................................................... Look for duplicate records .............................................

Examine employee salaries and bonuses .............................. Find total bonuses paid in 2002 ..................................... View salaries in descending and ascending order .........

.book Page iii Wednesday, July 5, 2006 1:42 PM

iii

View employees hired in 2002 ................................................................................... 16 Determine bonus as a percentage of salary ............................................................... 17 Exercises ..................................................................................................................... 18

Chapter review .................................................................................................................. 19

Chapter 3: Set up your project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Start your project in ACL ................................................................................................. 22

Create a project in ACL ............................................................................................. 22 Import your data files as tables .................................................................................. 23 Exercise ...................................................................................................................... 26

Check and verify the data ................................................................................................. 27 Check the data type and format ................................................................................ 27

Verify the data in a table ........................................................................................... 29 Document your work ............................................................................................... 30 Add record notes to a table ....................................................................................... 31 Exercise ..................................................................................................................... 31

Chapter review ................................................................................................................. 32

Chapter 4: Begin Your Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 D E F C E C

Ch F

Ch D

Tutorial.book Page iv Wednesday, July 5, 2006 1:42 PM

etermine the total liability ............................................................................................. 34 xtract expiring card numbers ........................................................................................ 34 ind transactions by customer number .......................................................................... 35 heck the credit card balances ........................................................................................ 37 xercises ........................................................................................................................... 39 hapter review ................................................................................................................. 39

apter 5: Examine expense patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 ind the total expenses for each category code ............................................................... 42

Summarize the total expenses for each category ..................................................... 42 Isolate transactions by category ............................................................................... 43 Isolate particular recreational expenses ................................................................... 45 Exercises .................................................................................................................... 46

ist the amounts for each card number ........................................................................... 46 Exercise ..................................................................................................................... 47

hapter review ................................................................................................................. 48

apter 6: Analyze transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 etermine which transactions are unacceptable ............................................................ 50

About the Join and Relations commands ................................................................ 50 Check the code tables for obvious errors ................................................................. 51 Identify unacceptable transactions .......................................................................... 53

dentify the employees ..................................................................................................... 56 Identify the employees’ departments ....................................................................... 57 Create a list of employees ......................................................................................... 59 Report your findings ................................................................................................. 61 Exercises .................................................................................................................... 62

........................................... 62

. . . . . . . . . . . . . . . . . . . . . . . 63 ........................................... 64 ........................................... 64 ........................................... 65 ........................................... 66 ........................................... 67 ........................................... 67 ........................................... 68 ........................................... 69 ........................................... 69 ........................................... 70 ........................................... 70 ........................................... 71 ........................................... 72 ........................................... 73 ........................................... 73 ........................................... 74 ........................................... 74 ........................................... 75

Tutorial

Chapter review .......................................................................

Chapter 7: Validate, correct, and extract data . . . . . . . . . . . . . Import the May transaction data ...........................................

Use folders to organize your project .............................. Import transaction data for May 2003 ........................... Compare and correct the table layouts ..........................

Combine and verify the tables ............................................... Combine the May transaction tables ............................. Verify the new table ........................................................ Check the combined table for duplicates ....................... Exercises ..........................................................................

Isolate and correct validity errors .......................................... Check the format of card numbers ................................ Create a 16-digit card number field ............................... Extract the corrected records to a new table .................. Export the combined transaction table to Excel ............ Create an error-free employee record table ................... Exercises ..........................................................................

Ideas for further analysis ....................................................... Chapter review .......................................................................

.book Page v Wednesday, July 5, 2006 1:42 PM

Tutorial.book Page vi Wednesday, July 5, 2006 1:42 PM

CHAPTER 1

PORATION 1 lysis scenarios

cords and poration. As apply ACL’s ou finish, you

In this c

Review th

About Me

Look at y

Plan your

INTRODUCING METAPHOR COR Experiment with real-world data ana

ACL in Practice is a tutorial that examines human resources re credit card transactions at a fictional company, Metaphor Cor you work through the procedures and examples, you learn to analysis and reporting capabilities to real-world data. When y will be able to apply the power of ACL to your own data.

hapter…

e tutorial objectives

taphor Corporation

our files

project

2 ACL in Practice

■ nd information

ansactions.

it exposure of the

umbers are due to iod, and extract this

nked to a particular

ation about the

ns”

emization of

erchant category

irfare, hotel stays,

ing establishments

mounts per credit

ts per customer

apter 1: Introducing Metaphor Corporation

Tutorial objectives In this tutorial you will:

■ Work with an existing data analysis project

■ Create and work with a new ACL project from start to finish

■ Report results of your ACL project

■ Export the results to another file format

These steps are typical for many data analysis projects that you perform in ACL, both small and large.

Chapter 2: “Examine employee data”

Objective: Examine employee profile data for Metaphor Corporation.

Summary:

■ Examine the project and its contents

■ Create a statistical overview

■ Examine employee bonuses and salaries

Chapter 3: “Set up your project”

Objective: Create the project and add the data.

Summary:

■ Create an ACL project.

■ Import the data into your project. Each data source is a separate table in the ACL project.

■ Verify the data.

■ Document your work.

Chapter 4: “Begin Your Analysis”

Objective: Determine basic backgrou about the credit card numbers and tr

Summary:

■ Determine the total credit lim company.

■ Determine which credit card n expire within a given time per information.

■ Find which transactions are li customer.

■ Determine some basic inform credit card balances.

Chapter 5: “Examine expense patter

Objective: Find information about it expenses and spending patterns.

Summary:

■ Determine transactions per m code.

■ Separate the transactions for a or rental cars.

■ Determine expenses for drink and golf courses.

■ Determine total transaction a card number.

■ Determine transaction amoun code.

About Metaphor Corporation

orporation ld software and IT services.

consultants who are full-time ts work out of Metaphor’s vel the world in order to ning courses, and initiate and .

rative and professional staff In addition, Metaphor has ons around the world who

Tutorial

Chapter 6: “Analyze transactions”

Objective: Determine whether transactions are acceptable, unacceptable, or require approval.

Summary:

■ Determine which transactions are unacceptable, and which employees made these transactions.

■ Create a report that includes employees with unacceptable transactions, details of the transactions, and the employees’ departments.

Chapter 7: “Validate, correct, and extract data”

Objective: Expand your analysis to include more data files.

Summary: This portion of the tutorial extends the Metaphor Corporation scenario.

■ Import the May 2003 credit card transaction files as tables.

■ Clean up the data in the May 2003 credit card transaction tables.

■ Extract the repaired data to new tables.

■ Combine the two May 2003 credit card transaction tables into one.

■ Using the techniques you have learned, analyze the May 2003 credit card transactions.

■ About Metaphor C Since 1995, Metaphor has so Metaphor has several dozen employees. These consultan head office and they also tra install software, provide trai maintain sales relationships

Metaphor also has administ members in the head office. sales agents in various locati are not employees.

.book Page 3 Wednesday, July 5, 2006 1:42 PM

ACL in Practice 3

Chapter 1: Introducing Metaphor Corporation

4 ACL in Practice

M By using ACL to analyze the credit card transactions of ansactions that are

at might be

xpenses.

Office and professional

Dues for membership clubs

Dues for professional organizations

Dues for fraternal organizations

Books and periodicals

Computers, computer parts, and software

Office supplies

Tutorial.book Page 4 Wednesday, July 5, 2006 1:42 PM

etaphor credit card policies Most employees have a company credit card. When an employee purchases products or services for the company, he or she must use a company credit card.

Employees entertain clients and prospective clients as appropriate, both in Metaphor’s home city and while travelling. They are also responsible for their own professional development, and are expected to charge expenses to the company credit card for courses, books, journal subscriptions, and professional memberships. Some employees are also responsible for purchasing supplies for the office.

Thus, Metaphor Corporation incurs significant expenses in several categories:

■ Travel

■ Entertainment

■ Client service

■ Professional development

■ Internal office expenses

Metaphor’s policy is that all purchases must be assigned to a customer number. Although most of the customer numbers correspond to external clients, customer numbers that start with zero correspond to internal departments.

Employees are aware that certain categories of transactions are unacceptable. For example, employees must not use corporate credit cards for gambling. Although the company sometimes approves transactions in other categories, permission must be granted in advance. Some categories are prohibited entirely.

Metaphor employees, you can find tr contrary to policy and transactions th erroneous or fraudulent.

The following table shows allowable e

Travel Entertainment

Airfare

Hotel

Taxi

Restaurants

Rental cars

Long-distance bus tickets

Travel agencies

Toll and bridge fees

Restaurants

Drinking places

Boat charters/rentals

Caterers

Bakeries

Sporting events

Recreational services

Amusement parks

Golf courses

Tourist attractions

Theater tickets

Tour buses

Musicians

Bowling alleys

About Metaphor Corporation

sis for your own company, whatever file format is wever, you continue an ady been planned. Much of ady complete.

use these files:

db file has a list of acceptable hant Category Code list.

phor.xls lists credit card information.

ents.txt le contains a list of ir internal numbers.

ed text file lists the employee mbers, and credit card

t lists the credit card l 2003.

s.txt xt file contains a list of rom the Merchant Category

Tutorial.book Page 5 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Acceptable category codes The credit card analysis in this tutorial relies on the Merchant Category Code list, which is used by major credit card providers. It contains thousands of four-digit numeric codes, including unique codes for many major airlines and hotel chains, and numerous generic codes. Every credit card transaction has an accompanying category code assigned to it.

In the real world, credit card companies occasionally assign incorrect codes, so if you obtain questionable results, contact the merchant for verification.

For the purposes of this analysis, you can compare transaction codes to the lists of acceptable and unacceptable codes. For example, if a Metaphor employee uses his credit card to gamble, the transaction code indicates an unacceptable transaction.

The Policies.doc document, which accompanies the tutorial data, discusses appropriate credit card usage policies. The data analyst uses this document to determine what types of transactions to flag.

Metaphor data files When you perform an analy you often obtain the data in available. In this tutorial, ho analysis project that has alre the preliminary work is alre

For this analysis project, you

■ Acceptable_Codes.m This Access database codes from the Merc

■ Credit_Cards_Meta This Excel worksheet

■ Company_Departm This tab-delimited fi departments and the

■ Employees.cvs This comma-delimit names, employee nu numbers.

■ Trans_April.xls This Excel workshee transactions for Apri

■ Unacceptable_Code This tab-delimited te unacceptable codes f Code list.

Chapter 1: Introducing Metaphor Corporation

6 ACL in Practice

■ ade data, you do not stigation of the data. tigate the data you

d what format is it

data?

rly formatted? If to make it suitable?

m more than one

eed from the data

ject, look at the file u can open a t it looks like, or

contains millions of k at the data in its

ask your IS elds and the field the file to get a

ome aspects of 99 to 2002, the data ase that is in a form

Tutorial.book Page 6 Wednesday, July 5, 2006 1:42 PM

Plan your analysis Developing an analytical strategy is typically an iterative process. As you work with the data in ACL, some patterns will likely become apparent, which might lead to new strategies. Here is a typical cycle:

■ Plan the analysis

■ Analyze the data

■ Reconsider the analysis

■ Analyze the data again

As you develop and work through your analysis, you might decide that you need additional data. For example, you use hire date and salary information in your analysis, but once you assemble preliminary results, you decide to acquire employee absence records. You can always add more data to your project, but obtain as much raw data as you can before you begin your analysis.

An ACL analysis involves a project that contains one or more tables. These tables are imported from external data sources, such as Excel worksheets, Access databases, text files, Oracle databases, and so forth. In a typical corporation, various departments store and assemble data on a daily basis. You can use ACL to combine data from disparate sources to create a single coherent analysis.

Once you have your data in place, you can develop objectives for your analysis. Consider some possible approaches. Make a note of tests that you think might be useful.

Preliminary investigation Because the tutorial includes ready-m have to perform any preliminary inve In general, however, you should inves want to use before you proceed.

Consider these questions:

■ What data currently exists, an in?

■ Who entered the data?

■ Who maintains the data?

■ Which department owns this

■ Is the data accurate and prope not, can the data be modified

■ Can you combine the data fro source?

■ Can you derive the data you n you already have?

Before you import a file into your pro in its original format. For example, yo Microsoft Excel worksheet to see wha you can open a text file in Notepad.

In a large file, such as a database that records, it might be impractical to loo original format. In that case, you can department for a description of the fi sizes. If possible, review an excerpt of better understanding of its content.

For example, if you want to analyze s customer invoices over the period 19 might exist in a comprehensive datab

Plan your analysis

s when you begin to organize

with test data. There is little elf to using data samples

d data files of any size. cts of data might be useful ing an analytical strategy.

look at at one day’s worth of ze five year’s worth. Working rd file is much quicker than n-record file. When you have , you can apply it to a

m your data. The quality of rove with the quality of your any errors from your data

o ACL. If that is not possible, orrect the data.

ur data. If you aren’t sure of n or command will do, you ur data remains unharmed.

Tutorial.book Page 7 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

that is not easily accessible. You could ask your IS department for information about specific fields, which might look like this:

With this type of information, you know in advance how the data appears. For example, you know that the two date fields are formatted differently.

Although ACL is capable of handling difficult data, you can make the process easier for yourself by ensuring that your data is clean.

General tips to consider Consider the following step a new project.

■ Refine your strategy need to restrict yours because ACL can rea However, small extra when you are develop

For example, you can data before you analy with a thousand-reco working with a millio your strategy in place million-record file.

■ Eliminate errors fro your analysis will imp data. Try to eliminate before bringing it int you can use ACL to c

■ Experiment with yo what an ACL functio can try it anyway. Yo

Field Comments

Company Name 30 characters, Text

Amount 12 characters, Text

Date of Invoice 10 characters, Date, DD/MM/YYYY

Date_Payment 10 characters, Date, MM/DD/YYYY

Chapter 1: Introducing Metaphor Corporation

8 ACL in Practice

■

Tutorial.book Page 8 Wednesday, July 5, 2006 1:42 PM

Chapter review In this chapter you learned:

■ The tutorial objectives.

■ The Metaphor credit card policy.

■ How to plan a typical ACL project.

CHAPTER 2

YEE DATA 2 d hiring dates

oyee profile man resources s of ACL,

r who has orker

In this c

Examine

Tutorial.book Page 9 Wednesday, July 5, 2006 1:42 PM

EXAMINE EMPLO Analyze salaries, bonuses, an

In this chapter, you look at some Metaphor Corporation empl data. You analyze salaries, bonuses, hiring dates, and other hu information. In the course of this analysis, you learn the basic practice navigating the program, and analyze data.

In this scenario, you take over an ACL project from a coworke already started work on it. You analyze the data that your cow assembled.

hapter…

the employee list

salaries and bonuses

Chapter 2: Examine employee data

10 ACL in Practice

■ project ployee data by

at you import into ted into the project, iginal data source

a new project to oject. You open, h the same way that dows programs.

e to locate the folder. Select k Open.

e is at the left of the ight. On the les, scripts, and log.

ents_Metaphor taphor table lists all ts around the the Metaphor

fault folders, this file

Tutorial.book Page 10 Wednesday, July 5, 2006 1:42 PM

Examine the employee list When you look at the employee data, you can get some ideas about how you might analyze this data. In this example, the project has already been assembled for you. When you work with your own projects, you decide what data to include. You can also add more data at any time to an existing project.

Your task is to determine various financial and human resource information, based on the data in your ACL project.

The project you work with in this chapter represents a small-scale, but fairly typical data analysis project. This project has two tables that contain information about Metaphor employees. You perform the analysis based on this information.

Open and view the employee data You begin your examination of the em viewing the contents of the project.

An ACL project is a holder for data th ACL as tables. Once the data is impor it does not matter to ACL what the or was.

When you use ACL, you either create work with, or you open an existing pr save, and close projects in ACL in muc you manage documents in other Win

Open the employee data project

1 Open ACL.

2 Select File » Open Project. Brows C:\ACL Data\Sample Data Files Metaphor_Employee_Data. Clic

Notice that the Project Navigator pan window, and the table view is at the r Overview tab, you can access the tab

In this project there are two tables: Ag and Employee_List. The Agents_Me of Metaphor’s independent sales agen world. The Employee_List lists all of employees at the head office.

As you work through the tutorial, you might notice that the columns on your screen sometimes appear in a different order from the columns in the illustrations. To change the order of a column, click the field list heading in the dialog box with which you are working.

Tasks

View the employee data project.

Get a statistical picture.

Check for duplicate employee records.

If your installation of ACL did not use the de may be located elsewhere on your system.

Examine the employee list

mmands give you a quick ger tables, the information ics and Profile is often useful.

rks with numeric and date meric fields only.

ate statistics about the s ranges of hiring dates and

ployee list

List table active, select tatistics. The Statistics

in ACL, ensure that no column in ise, ACL will run the command on

Tutorial.book Page 11 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

View the contents of the employee data project

1 In the Overview, right-click Metaphor_Employee_Data.ACL and select Properties. Click the Notes tab.

Although this project contains no notes, you can add them at any time to record details of your work.

2 Close the Project Properties dialog box.

3 Double-click Agents_Metaphor in the Overview to open the table.

4 Select Analyze » Count Records. The Count dialog box appears. Click OK.

The count of 41 records appears at the bottom of your screen. Although you cannot see a difference on the screen, ACL wrote the information to the log.

5 Open the Employee_List table. Count the number of employees. How many employees are there? You should get 200.

Get a statistical picture The Statistics and Profile co statistical snapshot. With lar that you can get from Statist

The Statistics command wo fields. Profile works with nu

In this procedure, you gener Employee_List table, such a salaries.

Generate statistics on the em

1 With the the Employee_ Analyze » Statistical » S dialog box appears.

Before you run a command the view is selected. Otherw just the selected field.

Chapter 2: Examine employee data

12 ACL in Practice

2 Click the Statistics On button. The Selected Fields If you prefer, you can run the Statistics command on each sults separately.

an overview of the ovided by Statistics.

table

ive, select Analyze e dialog box

. ACL displays the

eric table columns

Tutorial.book Page 12 Wednesday, July 5, 2006 1:42 PM

dialog box appears. Click Add All.

3 Click OK.On the Output tab, ensure that Screen is selected, Click OK. ACL displays the results.

If you scroll down, you can see that the earliest hiring date is 01/05/1995 and the latest hiring date is 12/19/2002. Some of the information provided is not useful, such as the total of the HireDate field values, so you can disregard this information.

field separately, so that you see the re

The Profile command also gives you data that is more concise than that pr

Use Profile to get information about a

1 With the Employee_List table act » Statistical » Profile. The Profil appears.

2 Select all of the fields and click OK results.

Each of the values for each of the num is shown on the tab.

Examine the employee list

ng for duplicates in only one elds in the output screen to results.

t Screen. Click OK.

n, click a hyperlink. ACL f the Employee_List, details.

Employee_List to any duplicate employee

numbers

button to remove the filter. table appears again.

r Duplicates. The ppears.

e Selected Fields dialog box nd click the arrow button.

Tutorial.book Page 13 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Look for duplicate records One way to ensure the integrity of the data is to check for duplicate records.

This might be a more complicated task than it seems at first glance. One employee might be listed under two names, or under the same name twice. Two employees might have been assigned the same employee number inadvertently. The type of duplicates you want to check depends on the data that you are working with.

In the next procedures, you check for duplicates on the employee last name and the employee number.

Look for duplicate last names

1 With the Employee_List table active, select Analyze » Look for Duplicates. The Duplicates dialog box appears.

2 Click Duplicates On. The Selected Fields dialog box appears. Click Last_Name and click the arrow button. Click OK.

3 Click the List Fields button. The Selected Fields dialog box appears. Click the Add All button. Click OK.

Although you are checki field, include all of the fi help you to interpret the

4 On the Output tab, selec

ACL displays the results.

5 In the Last_Name colum displays a filtered view o showing that employee’s

You next decide to check the determine whether there are numbers.

Look for duplicate employee

1 Click the Remove Filter The full Employee_List

2 Select Analyze » Look fo Duplicates dialog box a

3 Click Duplicates On. Th appears. Select EmpNo a Click OK.

Chapter 2: Examine employee data

14 ACL in Practice

4 On the Output tab, select Screen. Click OK. aries and

ings from the

id out in 2002.

t to lowest, and

re hired in 2002.

r each employee in

der.

Tutorial.book Page 14 Wednesday, July 5, 2006 1:42 PM

5 View the Duplicates tab. No duplicate employee numbers are found.

■ Examine employee sal bonuses

You have been asked to find several th employee data:

■ The total bonuses that were pa

■ The salaries paid: from highes lowest to highest.

■ The number of people who we

■ The ratio of bonus to salary fo 2002.

Tasks

Find the total amount of paid bonuses.

View salaries in ascending and descending or

View employees hired in 2002.

Calculate bonus as a percentage of salary.

Examine employee salaries and bonuses

and ascending order ou view the data in sequential view, Quick Sort is a ur records in ascending or are finished with Quick turn the view to the order in e table.

scending and ascending order

t table active, right-click the elect Quick Sort

loyee salaries in descending uld like to see them in

lumn header. Select Quick

k Sort operations can be es, you might find Quick Sort n any column, so you can get data.

Tutorial.book Page 15 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Find total bonuses paid in 2002 Metaphor has a complex compensation structure. Because Metaphor is a project-oriented company, some employees have a compensation scheme that relies heavily on bonuses.

To find the total amount paid in bonuses in 2002, use the Total command.

Use the Total command to find total bonuses paid in 2002

1 Open the Employee_List table.

2 Select Analyze » Total Fields.

3 Click the Total Fields button. The Selected Fields dialog box appears. Select Bonus_2002 and click the arrow button. Bonus_2002 now appears in the Selected Fields list. Click OK. Click OK.

4 View the Total tab.

The figure shown on the Total tab is the total amount paid in bonuses in the year 2002. The total that appears should be 1,016,483.76.

Suppose you wanted to see what command you just entered to achieve this result. You can look at the log.

5 Click the Log tab at the bottom left of the ACL window.

You can see the commands that you performed on this project.

View salaries in descending The Quick Sort feature lets y order. Applicable only to the temporary means to view yo descending order. When you Sort, you can turn it off to re which the records exist in th

View employee salaries in de

1 With the Employee_Lis Salary column header. S Descending.

You can now see the emp order. You decide you wo ascending order.

2 Right-click the Salary co Sort Ascending.

If you have a large file, Quic lengthy. If you have small fil useful. You can Quick Sort o different perspectives on the

A command is the name of a specific action taken in ACL, such as the Statistics command. To learn more about commands, select Help » Index and look up “commands”.

Chapter 2: Examine employee data

16 ACL in Practice

Vi 5 Change the expression to read: D HireDate <=

and click OK.

w. Note that there lter. This means 17 ing to this list.

Tutorial.book Page 16 Wednesday, July 5, 2006 1:42 PM

ew employees hired in 2002 To determine how many employees were hired in 2002, you apply a filter to the view. A filter is a logical expression that you can type in the view’s Filter text box. ACL displays only those records that match the filter.

Create a filter to show employees that were hired in 2002

1 Click the Edit View Filter button. The Edit View Filter dialog box appears. You can use this dialog box to create and edit filters that apply to the current table.

2 In the Available Fields list, double-click HireDate.

3 Click >= in the middle pane.

4 Click Date. Locate January 1, 2002 and click OK.

The expression should read:

HireDate >= `20020101`

Note that any filter that involves dates uses reverse single quotation marks. You cannot use typical quotation marks when you use dates, and you cannot use this special quote mark in other expressions.

Although you know that no employees have been hired in 2003, you want to ensure that the time period that you selected is correct. You want a list of all employees hired between January 1, 2002 and December 31, 2002.

HireDate >= `20020101` AN `20021231`

6 Click OK.

7 View the filtered records.

8 Select Analyze » Count Records

Look at the bottom of the ACL windo are 17 records showing up with this fi employees were hired in 2002, accord

An expression is a set of operators and values that you use to perform calculations, specify conditions for a test, or create values that do not exist directly in the data. An ACL expression can be a combination of data fields or computed fields, operators, constants, functions, and variables.

Examine employee salaries and bonuses

on Builder dialog box Builder is the same as the ox that you used already.

nter the expression: 00) / Salary

nes the 2002 bonus as a ee’s 2002 salary.

999999.99%.

k the green checkmark.

indow.

ed the new field, it does not yee_List view.

column header and select Columns dialog box

alary and click the arrow Salary now appears in the

k OK.

L handles precision and accuracy ct Help » Index and look up

cimal expressions”.

Tutorial.book Page 17 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Determine bonus as a percentage of salary You want to determine the bonus as a percentage of salary for all employees. To do this, you add a computed field to the table layout.

A computed field is created with an expression. This expression can use one or more fields already in the table.

Add a computed field to the table layout

1 With the Employee_List table active, click the Remove Filter button to remove the filter.

2 Select Edit » Table Layout. The Table Layout window opens with the Edit Fields/Expressions tab displayed.

3 Click the Add a New Expression button on the left. The Edit Fields/Expressions tab changes so that you can enter the expression for the new computed field.

4 Enter Bonus_Percent_Salary in the Name field.

5 Click f(x). The Expressi appears. The Expression Edit View Filter dialog b

6 In the Expression box, e (BONUS_2002 * 100.

This expression determi percentage of the employ

7 Click OK.

8 In the Format list, enter

9 To save the changes, clic

10 Close the Table Layout w

Although you have creat yet appear in the Emplo

11 Right-click the CardNum Add Columns. The Add appears.

12 Select Bonus_Percent_S button. Bonus_Percent_ Selected Fields list. Clic

To learn more about how AC in decimal calculations, sele “controlling rounding in de

Chapter 2: Examine employee data

18 ACL in Practice

on just the Run the Statistics

e the Statistics and

the Bonus_2002

the Comm_2002 n descending order. able.

o Metaphor agents

justing column or right. You can ile you follow the

Tutorial.book Page 18 Wednesday, July 5, 2006 1:42 PM

When working in an ACL project, you might want to resize the widths of the columns. You can move the column separator to adjust the width of the column so that it occupies less space in the view. Alternatively, you can double-click the right side of the column heading to resize the column to the width of its visible display. To resize more than one column at a time, select multiple columns, then double-click the right side of a column heading.

13 Close the view and click Yes when ACL prompts you to save.

Exercises 1 Can you run the Profile command

HireDate in the Employee_List? command on just the HireDate.

What results do you get? When ar Profile commands useful?

2 Quick Sort the Employee_List on column.

3 Quick Sort Agents_Metaphor on column in ascending order, then i Run Statistics and Profile on this t

4 Find the total commissions paid t in 2002.

5 Make the view easier to read by ad widths and dragging columns left adjust the columns at any time wh tutorial.

Keep in mind that the ratio will be skewed for employees hired in 2002, because they did not work a full year at Metaphor. You can apply a filter to hide the records of employees hired in 2002.

Chapter review

Tutorial.book Page 19 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

■ Chapter review In this chapter you accomplished the following tasks:

■ Opening a project

■ Viewing the project contents

■ Using Statistics and Profile commands to analyze data

■ Using Quick Sort to sort the data in a specified order

■ Creating and applying filters

You have completed a small demonstration of what you can do with ACL. You work through more complex examples in subsequent chapters.

Chapter 2: Examine employee data

20 ACL in Practice

Tutorial.book Page 20 Wednesday, July 5, 2006 1:42 PM

CHAPTER 3

PROJECT 3 add the data

dit card rd data.

he source data rksheets, text each data then analyze it.

In this c

Manage p

Import fi

Check an

Tutorial.book Page 21 Wednesday, July 5, 2006 1:42 PM

SET UP YOUR Create the project and

Before you begin your analysis of Metaphor Corporation’s cre transactions, you must create a project and access the credit ca

An ACL project functions as a container for data. You import t into your project from existing data sources, such as Excel wo files, Access databases, dBASE files, and so forth. You import source as a separate table. Once you import the data, you can

hapter…

rojects in ACL

les into a project

d verify the data

Chapter 3: Set up your project

22 ACL in Practice

■ roject. You can save

as you do with a dsheet.

click Save.

s. You use this ct.

hen you use the Data turn to the screen that

Tutorial.book Page 22 Wednesday, July 5, 2006 1:42 PM

Start your project in ACL Because you are starting a new data analysis project, you begin by creating a project in ACL.

Create a project in ACL To begin your work in ACL, create a p a project or close an ACL project just word processing document or a sprea

Create a project in ACL

1 Select File » New » Project.

2 In the File name box, enter ACL_Tutorial_Metaphor and

The Data Definition Wizard appear wizard to import data into your proje

Tasks

Create a project in ACL.

Import your data files as tables.

Other data import scenarios.

If you need to change a previous selection w Definition Wizard, click Back until you re you want.

Start your project in ACL

orkbook ls contains only one

ook has more than one worksheet to import on the f the Data Definition

orksheet

. The Data Definition ext until the Select File to rs.

Trans_April.xls. Click Next.

ard as you did with the table as Trans_April.

g the other data files as tables for importing Excel

des.txt file

as two columns titled Codes entries are separated by tab

. Click Next until the Select x appears.

Unacceptable_Codes.txt.

orrect data type when you import ange the data type after you

Tutorial

Import your data files as tables Now that you have begun a project in ACL, you add data to it. In each instance, you use the Data Definition Wizard, but the process varies slightly, depending on the type of file that you import.

You import data from each of the six files listed under “Metaphor data files” on page 5. ACL creates and saves a table for each file in a .fil file.

For the first file that you import, the Data Definition Wizard is already open because it appeared when you saved the new project. Start by importing the Credit_Cards_Metaphor.xls file, which ACL recognizes as an Excel file.

Import the Credit_Cards_Metaphor.xls worksheet

1 On the Data Definition Wizard screen, click Next and proceed through the wizard until the Select File to Define dialog box appears. Browse to find the C:\ACL Data\Sample Data Files folder. If your installation did not use the default locations, this folder might be located elsewhere on your system.

2 Double-click Credit_Cards_Metaphor.xls.

3 Click Next repeatedly to progress through the wizard. ACL makes the correct default selections, so you do not have to change them. The Save Data File As dialog box appears.

4 Enter Credit_Cards_Metaphor. Click Save.

You get a listing of the fields with a description.

5 Click Finish. When a message appears that asks you to save the untitled table, click OK.

The Credit_Cards_Metaphor table opens in ACL.

In this example, the Excel w Credit_Cards_Metaphor.x worksheet. If an Excel workb worksheet, you select which Select Data Source screen o Wizard.

Import the Trans_April.xls w

1 Select File » New » Table Wizard appears. Click N Define dialog box appea

2 Locate and double-click

3 Proceed through the wiz previous table. Save this

4 Click Finish. Click OK.

The procedures for importin are similar to the procedure worksheets.

Import the Unacceptable_Co

Unacceptable_Codes.txt h and Description. The field characters.

1 Select File » New » Table File to Define dialog bo

2 Locate and double-click Click Next.

It is best to give each field the c it into ACL. However, you can ch import the file.

.book Page 23 Wednesday, July 5, 2006 1:42 PM

ACL in Practice 23

Chapter 3: Set up your project

24 ACL in Practice

3 On the File Format screen of the Data Definition Although the unacceptable codes are comprised of e codes as character

ader on the Edit have a Type of ake any changes.

f the file.

are prompted to

ars in ACL. You can

t file

as no header row. ata Definition

he Data Definition ard as you did for ct

ain, select rmat screen. Click

s screen, deselect cessary. Select TAB

s dialog box

name. Click Save.

ties.

Tutorial.book Page 24 Wednesday, July 5, 2006 1:42 PM

Wizard, select Delimited text file, and click Next.

The Delimited File Properties screen of the Data Definition Wizard appears.

4 Select TAB in the Field Separator column. The data separates correctly into two fields.

5 Select Use first row as field names.

The first row of the file contains the field names, which are Codes and Description in this instance. You can see the format of the file as defined in the wizard.

6 Click Next and save the table as Unacceptable_Codes.

The Edit Field Properties screen of the wizard appears. You can change the type of the data in each field. ACL has made assumptions about the data that are not necessarily correct.

7 On the Edit Field Properties screen of the wizard, the Codes field is selected by default. Select ASCII Text from the Type drop-down list.

digits, you want ACL to treat thes data, rather than numeric data.

8 Click the Description column he Field Properties screen. It should ASCII Text. You do not have to m

9 Click Next.

The wizard provides a summary o

10 Click Finish. Click OK when you save the table.

The Unacceptable_Codes table appe scroll through the contents.

Import the Company_Departments.tx

This file is similar in format to the Unacceptable_Codes.txt file, but it h You must add the field names in the D Wizard.

1 Select File » New » Table to open t Wizard. Proceed through the wiz the previous file, but this time sele Company_Departments.txt. Ag Delimited text file on the File Fo Next.

2 On the Delimited File Propertie Use first row as field names if ne as the Field Separator.

3 Click Next. The Save Data File A appears.

4 Enter Company_Dept as the File

You can now edit the field proper

Start your project in ACL

as Employees.

rties screen:

m field and the EmpNo field

e field to Date type.

d Bonus_2002 are Numeric

olumn titles if you want more hanging the titles does not .

e as Employees.

o the message.

ens in the view.

m the Acceptable_Codes

s Access database

. Proceed through the Data select the file.

ough the wizard. Since this ne table, Acceptable_Codes in the Select the Access

able_Codes.

o the message. The opens in the view.

Tutorial.book Page 25 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

5 On the Edit Field Properties screen, enter Dept_Name in the Name text box.

6 Click the Field 2 column header. Enter Dept_Code in the Name field. From the Type drop-down list, select ASCII Text.

7 Click Next. Click Finish. Click OK to the message.

The Company_Dept table opens in the view.

Import the Employees.csv file

1 Select File » New » Table to open the Data Definition Wizard.

2 Proceed through the wizard to define the Employees.csv file in much the same way that you defined the Unacceptable_Codes.txt file.

3 On the File Format screen, ensure that ACL imports the file as a Delimited text file. Click Next.

4 On the Delimited File Properties screen, ensure that:

■ Comma is selected as the Field Separator.

■ Use first row as field names is selected.

5 Click Next. Save the file

6 On the Edit Field Prope

■ Change the CardNu to ASCII Text type.

■ Change the HireDat

■ Ensure that Salary an (Formatted) type.

■ You can change the c user-friendly titles. C affect the field names

7 Click Next. Save the tabl

8 Click Finish. Click OK t

The Employees table op

You next import the data fro database.

Import the Acceptable_Code

1 Click File » New » Table Definition Wizard and Acceptable_Codes.mdb

2 Continue to proceed thr database includes only o is automatically selected Table/View list.

3 Save the table as Accept

4 Click Finish. Click OK t Acceptable_Codes table

Chapter 3: Set up your project

26 ACL in Practice

As you can see in the Overview at the left of your project,

name, g File » Save As. roject,

Tutorial.book Page 26 Wednesday, July 5, 2006 1:42 PM

the data from each file has been placed into separate tables. You should have six tables in your project:

■ Acceptable_Codes

■ Company_Dept

■ Credit_Cards_Metaphor

■ Employees

■ Trans_April

■ Unacceptable_Codes

Save your project

■ Click File » Save Project. You can also click the Save the Open Project button on the toolbar.

In order to work through this tutorial effectively, you must save your work as you move from one procedure to the next.

Just as with any Windows application, you should save your work in ACL every few minutes while you are working on a project. That way, you protect yourself from losing your work if there is a system crash. ACL automatically creates a backup copy of the working project as well. You should save your work when you add or remove data from your project.

Throughout the tutorial, you will be prompted to save changes to a table, particularly as you move from table to table, or when you close the project. Click Yes to save the work you have done on each table.

Exercise Save the existing project under a new ACL_Tutorial_Metaphor_Test, usin Continue working with the original p ACL_Tutorial_Metaphor.

You can stop following the tutorial at any point by closing and saving the project.

Check and verify the data

mat sis, check the table layout for ther:

ch field are correct.

ly defined.

s information about how the ok, and what data type ACL

ear is a matter of preference. de that you wanted all card th hyphens after each four cision was to display a string

ta type and formatting

h entry should have 16 digits, hout any extra characters. The d should have a data type of II.

h entry should be a date. ending on the dates, you might

nt to determine if all the dates within a given time period. The d should have a data type of e.

h entry should be numeric, with decimal places. The field uld have a data type of Numeric.

Tutorial.book Page 27 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

■ Check and verify the data Before you proceed with analyzing your data, you should always verify it. For example, if a field is supposed to contain dates, but also contains names, your analysis may be meaningless. ACL has several commands that you can use to verify your data.

Check the data type and for Before you begin your analy each table to determine whe

■ The data types for ea

■ The fields are proper

The following table provide fields in the tables should lo should assign to them.

The way the data should app For example, you could deci numbers to be formatted wi digits. In this project, the de sixteen characters long.

Tasks

Check the data type and format.

Verify the data in a table.

Document your work.

Files Da

Credit card numbers Eac wit fiel ASC

Dates Eac Dep wa fall fiel Dat

Transaction Amounts, Finance Charges, and Credit Limits

Eac two sho

Chapter 3: Set up your project

28 ACL in Practice

View and edit data definitions in a table.

or table.

ble Layout

out each of the data t. Look at Category ta has been defined. izontally to see all and rearrange

read.

etermine whether e.

aracter data, but the . Although it is

types as Character, ta type, for example, earn more about the Index and look up “data

Tutorial.book Page 28 Wednesday, July 5, 2006 1:42 PM

In this tutorial, you assign the ASCII data type to Merchant Category Codes and credit card numbers because you will not perform calculations with either field. You also require an ASCII data type for work that you do later in the tutorial. If you were performing mathematical tests on the card numbers, you would need a data type of Numeric.

Ideally, in a thorough data analysis, you verify the data in every field in every table of your project. If you like, you can set up a chart with the appropriate tests for each field, and keep a check list of the results. Each verification command is also shown in the log.

However, if you are confident that your data has been entered properly, you can skip the verification steps or verify only a few fields.

Often, you can use ACL to fix incorrectly formatted data. You can create computed fields that omit improper characters, for example.

In this chapter, you will modify the default data types that ACL selected, but the underlying data does not require repair.

1 Open the Credit_Cards_Metaph

2 Click Edit » Table Layout. The Ta window appears.

This window lists information ab fields as it is defined in the projec and Type to determine how the da You may need to scroll the list hor of the information. You can widen columns to make the list easier to

3 Look through the list of fields to d each field has the correct data typ

The CardNum field should be ch N indicates that it is numeric data

Names The entries should contain only letters, spaces, and hyphens. The field should have a data type of ASCII.

Merchant Category Codes Each code should be 4 digits long, with no extraneous characters. The field should have a data type of ASCII.

Field lists like this one categorize data Numeric, Date, or Logical. The ASCII da belongs to the Character category. To l categories of data types, select Help » type reference”.

Check and verify the data

ata types and the data are etermine whether the data

pe. Use the Verify command a table matches its data type.

ers in a field with a numeric ers in a field with a date data dicates data validity errors.

_Metaphor table.

Selected Fields dialog box

e fields you want to verify in

alidity errors.

lds in the table.

Tutorial.book Page 29 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

comprised of digits, you will not be performing mathematical operations with the card numbers.

4 Double-click the CardNum entry.

5 In the Type drop-down list, select ASCII.

6 Click the green checkmark to save the changes.

7 Close the Table Layout window.

Verify the data in a table Once you establish that the d correctly defined, you next d matches its assigned data ty to check whether the data in

For example, if you have lett data type, or decimal numb type, the Verify command in

Verify the data in a table

1 Open the Credit_Cards

2 Click Data » Verify.

3 Click Verify Fields. The appears.

4 Click Add All. Click OK

You should see a list of th the Verify Fields list.

5 Click OK.

You should have zero data v

You have now verified all fie

Chapter 3: Set up your project

30 ACL in Practice

tion

Tutorial.book Page 30 Wednesday, July 5, 2006 1:42 PM

cument your work When you work with an ACL project, you want the methods that you use in your work to be clear. You can then justify the results of your work, or allow someone else to take over the analysis tasks if necessary. With proper documentation, you can see at a glance what work you have done in ACL. This will make your job easier, particularly if you are responsible for multiple analysis projects.

If you are taking over a project from someone else, documentation is particularly valuable. Similarly, if you later assign the project to someone else, that person will appreciate being able to read historical project notes.

Add and edit project notes

1 Select the project name in the Overview.

2 Click File » Properties. The Project Properties dialog box appears.

3 In the Notes tab, enter the current date and time, and a short summary of what you plan to accomplish.

4 Click OK to close the Project Properties dialog box.

When you close the dialog box, ACL automatically saves the notes. However, if you accidentally erase the notes, you cannot get them back unless you have a file backup. For this reason, it is recommended that you use both a task sheet and the project notes for documentation.

If you find it helpful, you can adapt the following task sheet to your project to keep track of what analysis you have already done, and what analysis you plan to do. This task sheet is prepared and maintained outside of ACL.

Items Descrip

Information you need

Where to get this information

Project folder location

Related projects

Verification (create a checklist for each verified field)

Table names with extracted, validated data

Description of each analytical task in ACL

Analytical task #1 to n... (ACL steps)

Report for each objective

Reporting task #1 to n... (ACL steps)

Location and description of exported data files

Check and verify the data

other table in the ject.

Tutorial.book Page 31 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Add record notes to a table You can add notes to specific records in a table. These notes can contain any information that might be helpful to someone viewing the project, and you can also link to other files containing information.

1 Open the Employees table. Note the numeric cells that indicate the record number at the left of the table.

2 Right-click record cell 3 and select Edit Note. The Edit Note dialog box appears.

3 Enter this note: This employee is leaving Metaphor Corporation by the end of the year.

4 Click OK.

Any notes you add to a table do not affect the table data.

Exercise Verify the data for at least one ACL_Tutorial_Metaphor pro

Chapter 3: Set up your project

32 ACL in Practice

■

Tutorial.book Page 32 Wednesday, July 5, 2006 1:42 PM

Chapter review In this chapter you accomplished the following tasks:

■ Creating a project.

■ Importing files into your project.

■ Saving your project.

■ Verifying the data in your project.

■ Documenting your work.

You have not actually done any analysis of the data yet, but you have set the project up so that it is ready.

CHAPTER 4

ANALYSIS 4 g for patterns

t, started a ou now use

formation rd balances to card usage

In this c

Determin

Extract e

Find tran

Check cre

Tutorial.book Page 33 Wednesday, July 5, 2006 1:42 PM

BEGIN YOUR Start lookin

So far, you looked at a project plan, worked on a simple projec new project, added data to the project, and verified the data. Y ACL to perform simple analysis of your data.

In this chapter, you use simple procedures to obtain general in about your data. You look at the credit limits and the credit ca get information about Metaphor’s financial liability and credit patterns.

hapter…

e total liability

xpiring card numbers

sactions for a customer

dit card balances

Chapter 4: Begin Your Analysis

34 ACL in Practice

■ umbers nting department h the employees ter all credit card anuary 1, 2004 or

ab, click the Edit iew Filter dialog

filter:

select the date, if 1` directly.

Click OK. The s fit the criteria of , 2004 or earlier.

redit card numbers charge of credit

Tutorial.book Page 34 Wednesday, July 5, 2006 1:42 PM

Determine the total liability If every Metaphor employee reached the credit limit on his or her credit card, what would be the total debt for Metaphor? You can determine this figure by totalling the CREDLIM field.

You can use Total to add the total credit limits together to determine the company’s total credit exposure.

Total the CREDLIM field

1 Open the Credit_Cards_Metaphor table.

2 Select Analyze » Total Fields. The Total dialog box appears.

3 Select the CREDLIM field. You only want to total this field, so there is no need to select any other fields.

4 Click OK. View the Total tab.

What is your result? Your total should be 1,440,800.

You can export the results to another file format if you want to save the result. You can also view the results in the log if you come back to the project later.

■ Extract expiring card n You plan to coordinate with the accou to ensure that there is no problem wit getting new credit cards. First, you fil numbers that have an expiry date of J earlier.

Filter card numbers by expiry date

1 On the Credit_Card_Metaphor t View Filter button. The Edit V box appears.

2 In the Expression box, enter this

EXPDT <= `20040101`

You can click the Date button and you prefer not to enter `2004010

3 Click OK.

4 Select Analyze » Count Records. status bar shows that 8/200 record having an expiry date of January 1

You want to send the soon-to-expire c that appear in the view to the clerk in

Find transactions by customer number

y customer

about any expenditures 4413, you want to find

ansactions associated with

n at least two different

FIND( ) function that th a CUSTNO value of ny of ACL’s functions to ed field. Use FIND( ) to

USTNO equal to 444413.

however, check the data the Trans_April table.

from Excel, ACL made data types for the fields. e of the data types.

le.

The Table Layout window

filter or an expression for a ctions in the Expression

Tutorial.book Page 35 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

cards. In the next procedure, you export the credit card numbers to a text file.

Export soon-to-expire credit card numbers

1 Click Data » Export to Other Application. The Export dialog box appears.

2 Select View. From the Export As drop-down list, select Text.

3 Enter a descriptive filename in the To text box, such as CCN_To_Expire_by_January1.txt. Click OK.

You can then attach the text file to an e-mail, print it out, or manage it in whatever way you want.

■ Find transactions b number

Because you have been warned related to customer number 44 some information about the tr that customer number.

You can accomplish this task i ways:

■ Create a filter using the looks for all records wi 444413. You can use a build a filter or comput isolate specific records.

■ Create a filter that sets C

Before you can do the search, types assigned to the fields in Because you imported the data default assumptions about the You might need to change som

Edit the Trans_April data types

1 Open the Trans_April tab

2 Click Edit » Table Layout. appears.

You use functions to build either a computed field. You can see the fun Builder.

Chapter 4: Begin Your Analysis

36 ACL in Practice

Use the FIND( ) function to locate customer records

, click the Edit View ilter dialog box

expression:

444413 because e of ASCII.

e appears that lid. Click OK. If the nd verify it again.

essions you create,

that the CUSTNO 13.

Click OK. 26/281 44413. In a typical

nd out who this s that come with the ation.

substrings within a ",CUSTNO) would

Tutorial.book Page 36 Wednesday, July 5, 2006 1:42 PM

■ The AMOUNT field has the correct data type of NUMERIC.

■ The CARDNUM and CODES fields should be edited to have a data type of ASCII.

■ The CUSTNO and DESCRIPTION fields have the correct data type of ASCII.

■ The TRANS_DATE field has the correct data type of DATE.

3 Double-click CARDNUM. From the Type drop-down list, select ASCII. Click the green checkmark. Repeat for CODES.

4 Close the Table Layout window.

Now that you have checked and corrected the data types, you can proceed to use the FIND( ) function.

1 With the Trans_April table active Filter button. The Edit View F appears.

2 In the Expression box, enter this

FIND("444413",CUSTNO)

Quotation marks are used around the CUSTNO field has a field typ

3 Click the Verify button. A messag indicates that the expression is va expression is not valid, correct it a You do not have to verify the expr but doing so is recommended.

4 Click OK. Scroll to the right to see field only contains values of 4444

5 Select Analyze » Count Records. records have a CUSTNO value of 4 analysis, you would likely try to fi customer is. However, the data file tutorial do not contain this inform

You can also use FIND( ) to search for field. For example, the filter FIND("00

Check the credit card balances

rd balances ew of your data to alert you or example, you might es, or find balances over a nformation as an indicator ployee credit card usage.

ny credit card users have r the records to show those than 2000 and a NEWBAL an also sort the balances to e.

balances that exceed $1000

etaphor table. Click Yes if he changes to the

column header. You may to locate the column. Select

scending order, with the through the table, you can ous balances are.

button. The Edit View

Expression box:

Tutorial.book Page 37 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

isolate all instances where 00 appears in the customer number field, whether at the beginning, middle, or end.

Setting a filter directly

1 With the Trans_April table active, click the Remove Filter button to remove the filter.

2 Click the Edit View Filter button. The Edit View Filter dialog box appears. Enter the expression:

CUSTNO = "444413"

3 Click OK.

You get exactly the same results with this filter as you did using the FIND( ) function.

4 Click the Remove Filter button to remove the filter.

When you apply a filter to data, and you want to save the results, you have several options. You can:

■ Extract the filtered records to another table.

■ Create a report containing the filtered records.

■ Reapply the filter to the original data whenever you want to look at it again.

As long as you have the original data, you can always reapply the filter to see the filtered data again.

ACL typically provides multiple methods for performing any given task. If you want to be sure that your reasoning is correct, you can perform a task in more than one way and check to see if you get the same result both times.

■ Check the credit ca You might get a general overvi to possible areas of concern. F want to find the highest balanc certain limit. You can use this i of potential problems with em

One way to determine how ma high balances would be to filte with a NEWBAL value greater value greater than 1000. You c see the balances in order of siz

Filter the NEWBAL field to find or $2000

1 Open the Credit_Cards_M ACL prompts you to save t Trans_April table.

2 Right-click the NEWBAL need to scroll horizontally Quick Sort Descending.

ACL lists the balances in de top value first. By scrolling get an idea of what the vari

3 Click the Edit View Filter Filter dialog box opens.

4 Enter this expression in the

NEWBAL > 2000

5 Click OK.

Chapter 4: Begin Your Analysis

38 ACL in Practice

The balances greater than $2000 are listed in If you want, you can extract the filtered records to a new

Sort to see credit

n to remove the

d select Quick Sort

that many credit t charges.

s of how ACL can u gain more facility k of new ways to

Tutorial.book Page 38 Wednesday, July 5, 2006 1:42 PM

descending order.

6 Select Analyze » Count Records. Click OK.

The status bar shows 53/200 records that have a NEWBAL amount greater than $2000.

Next, you change the filter to look at records with a NEWBAL amount greater than $1000.

7 In the Filter field at the top of the view, change the filter to NEWBAL > 1000 and press ENTER.

8 Select Analyze » Count Records. Click OK.

You should have 65 records that fit the criteria.

You can have more than one condition in your filter. In addition to the transaction amounts, the amounts in the PASTDUEAMT and FINCHG fields might indicate a problem with credit card usage. According to the Policies.doc document, Metaphor credit card users must pay off their balances every month.

9 Change the filter to:

(NEWBAL > 1000) OR (PASTDUEAMT > 0) OR (FINCHG > 0)

10 Count the filtered records. You should have 89.

table, or print a report.

You next decide to try another Quick cards with past due amounts.

Quick Sort on PASTDUEAMT

1 Click the Remove Filter butto filter.

2 Right-click on PASTDUEAMT an Descending.

3 Scroll through the records. Notice card holders have past due amoun

These procedures are minor example analyze a large amount of data. As yo with ACL, you will undoubtedly thin analyze your data.

Exercises

total credit card financial

umbers that are due to ted them to a text file.

ked to a particular

field to determine how eded particular limits.

Tutorial.book Page 39 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

■ Exercises 1 Extract the records with a NEWBAL value greater

than 1000 to a new table for later analysis.

2 What is the difference between the total number of transactions and the number of transactions with a NEWBAL value greater than 2000?

3 Of the credit card numbers that have a NEWBAL value greater than 2000, how many have a PASTDUEAMT greater than zero?

4 Check for card numbers with a zero NEWBAL value. Export these to a text file.

5 Check the Credit_Card_Metaphor table for all card numbers with a negative NEWBAL value. Export these to an Access file.

6 Under the terms of the credit card program, Metaphor can avoid finance charges if employees submit their receipts on time to the accounting department. Metaphor wants to determine how much money it loses unnecessarily from finance charges. Total the FINCHG field. Also, how many credit card holders have a FINCHG value that is greater than $10.00?

7 Metaphor wants to know the total of all current balances. In the Credit_Card_Metaphor table, total the NEWBAL field.

8 Metaphor is considering reducing its upper credit limit. How many credit card numbers have credit limits exceeding $10,000?

■ Chapter review In this chapter, you:

■ Calculated Metaphor’s liability.

■ Found the credit card n expire soon and extrac

■ Found transactions lin customer.

■ Checked the NEWBAL many transactions exce

Chapter 4: Begin Your Analysis

40 ACL in Practice

Tutorial.book Page 40 Wednesday, July 5, 2006 1:42 PM

CHAPTER 5

PATTERNS 5 bout expenses

rity with your . Now the

o provide ransactions. If form analyses

etaphor e cases, or by

categories of basis so that is being spent

In this c

Find the t category

Determin informati

Tutorial.book Page 41 Wednesday, July 5, 2006 1:42 PM

EXAMINE EXPENSE Determine information a

In the previous chapters, you worked with ACL to gain familia data and you performed some preliminary analysis of the data accounting department at Metaphor Corporation wants you t information about spending patterns in the April credit card t you can develop a useful analysis plan, you will be asked to per for other months.

Once you provide information about the spending patterns, M might be able to economize by choosing a single vendor in som obtaining bulk discounts, or by further limiting the acceptable transactions. You will also look at expenses on a per-customer Metaphor can use the results to determine whether the money effectively.

hapter…

otal expenses for each code

e specific transaction on

Chapter 5: Examine expense patterns

42 ACL in Practice

■ each category unts for each how much money cific hotel chains. hand, will be e Merchant hers are broad.

tegory code

Summarize dialog

CODES.

MOUNT. You each category code.

SCRIPTION. You n the new table.

the Name text box, k OK.

cted to the

Tutorial.book Page 42 Wednesday, July 5, 2006 1:42 PM

Find the total expenses for each category code

You begin your analysis by summarizing the total amount of all the transactions for each category code. Your plan is to look at these results to get ideas for further analysis.

Summarize the total expenses for By summarizing the transaction amo merchant category code, you can see was spent on specific airlines and spe All restaurant expenses, on the other categorized under a single code. Som Category Codes are specific, while ot

Summarize transaction amounts by ca

1 Open the Trans_April table.

2 Select Analyze » Summarize. The box appears.

3 In the Summarize On list, select

4 In the Subtotal Fields list, select A want to total the amount spent on

5 In the Other Fields list, select DE want to see what the codes mean i

6 On the Output tab, select File. In enter Trans_by_Code and clic

The summarized results are extra Trans_by_Code table.

Tasks

Summarize the total expenses for each category code

Isolate transactions for airfare, hotel stays, and rental cars

Isolate particular recreational expenses

Find the total expenses for each category code

gory closely at the total amount n April, specifically on d rental cars.

nd airfare fall exclusively in ll transactions with these

by Metaphor employees.

es by filtering and displaying etween and including 3000 to

mation is by using the he CODES field.

eric, date, and character ng form:

max)

( ) function to capture all e range 3000 to 3750. In the ES field has extra spaces, so

M( ) function to trim the

n to find transactions in a

able.

r button. The Edit View s.

in the Expression box:

DES),"3000","3750")

box indicates that the OK.

Tutorial.book Page 43 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

View the new table. Note that the COUNT field indicates the number of transactions per code. You can do a Quick Sort Descending on the AMOUNT field to see where the most money was spent. You can also use this table for further analysis.

This summary table is useful for showing the amounts spent on airlines, hotel chains, rental car agencies, and other categories. However, it does not show you the total amount spent on each of airfare, hotels, and rental cars, nor does it tell you the total amount spent on travel.

Often, when you first perform an analytical task, you do not get precisely the results you want. However, you can use those results to help you find a way to achieve more focused results.

Isolate transactions by cate Metaphor wants you to look spent on out-of-town stays i airfare, accommodation, an

Hotels, motels, rental cars, a the code range 3000-3750. A codes should relate to travel

You can isolate these expens all transactions with codes b 3750.

One way to isolate this infor BETWEEN( ) function on t BETWEEN( ) works on num data, and it takes the followi

BETWEEN(value,min,

You will use the BETWEEN transactions with codes in th Trans_April table, the COD you have to use the ALLTRI CODES field.

Use the BETWEEN( ) functio given range

1 Open the Trans_April t

2 Click the Edit View Filte Filter dialog box appear

3 Enter the following filter

BETWEEN(ALLTRIM(CO

4 Click Verify. A message expression is valid. Click

Chapter 5: Examine expense patterns

44 ACL in Practice

If the expression is not valid, look at it carefully again, You can now perform further analyses of these filtered etermine the total erchant Category d 3299, you can use lues between 3000

Tutorial.book Page 44 Wednesday, July 5, 2006 1:42 PM

particularly the quotation marks, and make the necessary changes.

5 Click OK. The Edit View Filter dialog box closes. You now have a filtered view of the transactions that displays transactions with Merchant Category Codes between 3000 and 3750.

6 Select Analyze » Count Records. Click OK. How many records fit this criteria?

7 Select Analyze » Total Fields. The Total dialog box appears. Select the AMOUNT field. Click OK.

ACL displays the total of all transactions for airfare, accommodation, and rental cars. You should have a total of 47,248.27.

To facilitate further analysis of these transactions, you can extract the filtered transactions to a new table.

8 Select Data » Extract Data. The Extract dialog box appears.

9 Ensure Record is selected. Enter Trans_April_Travel in the To field. Click OK.

transactions. For example, you can d spent on airfare alone. Because the M Codes for airfare are between 3000 an the BETWEEN( ) function to find va and 3299.

Find the total expenses for each category code

tatus bar indicates that ATCH( ) parameters.

and to find the total spending could apply further filtering. ansactions to a new table.

Tutorial.book Page 45 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Isolate particular recreational expenses So far you have looked at travel-related expenses for April. Metaphor now wants you to look at the total expenses related to golf and drinking establishments. You need to isolate those transactions with a Merchant Category Code that is related to golf or drinking establishments.

The MATCH( ) function provides a way to apply multiple filters to a table. The filters must all relate to one field, which is CODES in this example. You could achieve the same results by using a filter with multiple OR statements, but the filter would be more difficult to set up and understand at a glance.

Again, you use ALLTRIM( ) to trim the codes before comparison.

Use the MATCH( ) function to isolate recreational expenses

1 Open Trans_April.

2 Click the Edit View Filter button. The Edit View Filter dialog box appears.

3 In the Expression box, enter the following expression:

MATCH(ALLTRIM(CODES),"5813","7992")

Note that the syntax for the MATCH( ) function is:

MATCH (comparison_value, test1, test2 <,test3 ...> )

5813 is the code for Drinking Establishments.

7992 is the code for Public Golf Courses.

You could add more MATCH( ) parameters to search for more codes if you wanted.

4 Click OK.

5 Count the records. The s 11/281 records fit the M

You can use the Total comm on these two categories. You You could also extract the tr

Chapter 5: Examine expense patterns

46 ACL in Practice

Ex ach card

ransactions, you n amounts for each

April. You can get command.

ove any filters, if

sify On list. Click list.

ter me field. Click OK.

lists the s per card number. r of transactions per

Tutorial.book Page 46 Wednesday, July 5, 2006 1:42 PM

ercises 1 Total the expenses for airfare, hotel stays, and rental

cars separately.

2 Total the expenses for all British Airway flights.

3 Total food-related expenses. How much money did the company spend on restaurants, fast-food restaurants, and catering, both separately and together?

4 Use MATCH( ) to find transactions billed to internal departments. Internal departments have customer numbers that begin with zero.

5 Determine what percentage of the total transaction amounts is billed to internal departments, and what percentage is billed to external clients.

■ List the amounts for e number

Now that you have a complete list of t want to determine the total transactio credit card number for the month of this information by using the Classify

Use the Classify command

1 Open the Trans_April table. Rem applicable.

2 Select Analyze » Classify.

3 Select CARDNUM from the Clas AMOUNT in the Subtotal Fields

4 On the Output tab, select File. En Trans_April_by_CCN in the Na View the new table.

The Trans_April_by_CCN table accumulated transaction amount The COUNT field lists the numbe card number.

List the amounts for each card number

d to create a table that lists a per-customer-number

Tutorial.book Page 47 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

5 Right-click the Percent of Count column and select Remove Selected Columns. Do the same for the Percent of Field column.

6 Close the view and click Yes when ACL prompts you to save.

Exercise Use the Classify comman the total transactions on basis.

Chapter 5: Examine expense patterns

48 ACL in Practice

■

Tutorial.book Page 48 Wednesday, July 5, 2006 1:42 PM

Chapter review In this chapter you accomplished the following tasks:

■ Determined the total transaction amounts for each merchant category code.

■ Determined transaction amounts for specific expense categories.

■ Determined the total transaction amounts per credit card number.

CHAPTER 6

SACTIONS 6 e transactions

alysis of the ther Metaphor proved ards to buy

In this c

Determin are unacc

Identify t made una

Report yo

Tutorial.book Page 49 Wednesday, July 5, 2006 1:42 PM

ANALYZE TRAN Identify unacceptabl

So far you have performed a preliminary investigation and an Metaphor data in your ACL projects. You now determine whe employees are using their company credit cards to make unap purchases, and whether they are using their company credit c specifically forbidden items and services.

hapter…

e which transactions eptable

he employees who cceptable transactions

ur findings

Chapter 6: Analyze transactions

50 ACL in Practice

■ ands data tables, you field values exist in

you might have a ed airfare and a list otel rooms. You omers purchased rom your agency.

epartment store, ers who purchased re and a list of ds of over $1000.

omers purchased btained a refund of

mands to compare s. With these

ne table, but not in

oth tables.

two source tables.

le that contains data esulting table looks ct.

sically combine the g two or more related tables as if able. You can create f the related tables. ble to the view of

Tutorial.book Page 50 Wednesday, July 5, 2006 1:42 PM

Determine which transactions are unacceptable

Metaphor Corporation has created three categories of transaction codes, based on the Merchant Category Codes list. These include:

■ Acceptable

■ Unacceptable

■ Conditional (requires prior approval)

One table lists the acceptable codes and one table lists the unacceptable codes. Codes that are not listed in either table are conditional.

To find all unacceptable transactions, proceed through the following tasks.

About the Join and Relations comm When your project includes multiple often want to find whether particular more than one table. Examples:

■ If you manage a travel agency, list of customers who purchas of customers who purchased h can determine how many cust both airfare and hotel rooms f

■ If you work in marketing at a d you might have a list of custom over $2000 last year at your sto customers who obtained refun You can determine which cust over $2000 last year and also o over $1000.

You can use the Join and Relations com tables and to combine data from table commands, you can:

■ Find field values that exist in o another.

■ Find field values that exist in b

■ Combine specified fields from

The Join command creates a new tab from both of the original tables. The r like any other data table in your proje

The Relations command does not phy tables, but creates a virtual join amon tables. You can use the fields from the all of the fields belonged to the same t expressions that use fields from any o You can add fields from one related ta

Tasks

Check the code tables for obvious errors.

Identify transactions that have unacceptable codes.

Identify the employees who made the unacceptable transactions.

Create a report of your results.

Determine which transactions are unacceptable

vious errors tables to check the s for obvious errors. You will termine whether the d the Unacceptable_Codes mon.

mand, you must ensure that re character fields that are the

of the key fields

_Codes table.

ut. The Table Layout

at the data type of the Codes length is equal to either the or the length of the longest ver is longer.

indow.

odes table.

ut. The Table Layout

at the data type of the Codes ld length is 50.

es are each four characters elds is padded with extra the two fields by creating

h table. These new fields will:

its of the Codes field as

f 4.

Tutorial.book Page 51 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

another related table so that you can see fields from both tables in a single view. If you want, you can then extract any or all of the fields from the related tables to a new table.

Because the Join command creates a new table based on the two original tables, you generally use the Relations command instead of Join if the original tables are very large.

Check the code tables for ob Before you can use the code transactions, check the table use the Join command to de Acceptable_Codes table an table have any codes in com

Before you use the Join com the key fields of both tables a same length.

Compare the length and type

1 Open the Unacceptable

2 Select Edit » Table Layo window appears.

The list of fields shows th field is ASCII. The field length of the field name, value in the field, whiche

3 Close the Table Layout w

4 Open the Acceptable_C

5 Select Edit » Table Layo window appears.

The list of fields shows th field is ASCII, but the fie

The Merchant Category Cod long. Each of these Codes fi spaces. You must harmonize computed code fields in eac

■ Contain the four dig character data.

■ Have a field length o

To learn more about Join and Relations, select Help » Index and look up “combining tables”.

Chapter 6: Analyze transactions

52 ACL in Practice

You can then perform the join successfully by using the 8 Close the view and click Yes when ACL prompts you

able. Repeat steps 2–

ed, you can join the ceptable field from

he e

codes in common

able.

n dialog box

as the Primary le as the Secondary

and Description in e any codes that le to see their

in the To field. that will be created.

Tutorial.book Page 52 Wednesday, July 5, 2006 1:42 PM

computed fields as the key fields.

Create a computed field in each of the Acceptable_Codes and Unacceptable_Codes tables

1 Open the Acceptable_Codes table, if not already open.

2 Open the Table Layout window, and click the Add a New Expression button.

3 In the Name text box, enter Codes_Sub_Acceptable.

4 Click f(x) to open the Expression Builder. In the Expression text box, enter:

SUBSTR(ALLTRIM(Codes),1,4)

■ The ALLTRIM( ) function trims any extraneous spaces that occur on the left or the right of the Codes field.

■ The SUBSTR( ) function reads the first four characters of the Codes field.

5 Click the green checkmark. The new field, Codes_Sub_Acceptable, appears in the Table Layout window. Verify that the category is C for character and that the field length is 4.

6 Close the Table Layout window.

You can now add the Codes_Sub_Acceptable field to the view.

7 Right-click the Codes column header. Click Add Columns. The Add Columns dialog box appears. Double-click Codes_Sub_Acceptable and click OK.

The Codes_Sub_Acceptable column appears in the view.

to save.

9 Open the Unacceptable_Codes t 7, but call the computed field Codes_Sub_Unacceptable.

Now that the key fields are harmoniz tables by using the Codes_Sub_Unac the Unacceptable_Codes table and t Codes_Sub_Acceptable field from th Acceptable_Codes table.

Determine whether the tables have any

1 Open the Unacceptable_Codes t

2 Select Data » Join Tables. The Joi appears.

3 In the Secondary Table list, selec Acceptable_Codes.

4 Select Codes_Sub_Unacceptable Key. Select Codes_Sub_Acceptab Key.

5 Select Codes_Sub_Unacceptable the Primary Fields list. If there ar exist in both tables, you will be ab description.

6 Select Presort Secondary Table.

7 Enter Codes_in_both_tables This is the name of the new table

Determine which transactions are unacceptable

actions ains these fields:

able contains the following

table (a computed field)

nsactions, you need to relate he Unacceptable_Codes st ensure that the key fields ta type of ASCII and that

gths.

created a computed field table in the

and you know that the length table field is 4 and that the ou need to check the CODES le to check its data type and

s_April

able.

ut.

Tutorial.book Page 53 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

8 On the More tab, select Matched Primary Records, and ensure that no other Join Categories options are selected. Click OK.

9 ACL displays the new table. It should be empty.

Although you can see that the table is empty, you should still count it in order to record the results in the command log and leave an audit trail.

10 Select Analyze » Count Records. Click OK.

Because there are no records in the Codes_in_both_tables table, you know that no codes exist in both the Unacceptable_Codes and Acceptable_Codes tables. The original code tables have no obvious errors, so you can use them for your analysis.

Identify unacceptable trans The Trans_April table cont

■ CARDNUM

■ AMOUNT

■ TRANS_DATE

■ CODES

■ CUSTNO

■ DESCRIPTION

The Unacceptable_Codes t fields:

■ Codes

■ Description

■ Codes_Sub_Unaccep

To identify unacceptable tra the Trans_April table with t table. However, you must fir for each table both have a da they both have the same len

In the previous section, you called Codes_Sub_Unaccep Unacceptable_Codes table, of the Codes_Sub_Unaccep category is C for character. Y field in the Trans_April tab length.

View the table layout for Tran

1 Open the Trans_April t

2 Select Edit » Table Layo

Chapter 6: Analyze transactions

54 ACL in Practice

The list of fields shows that the CODES field has a 7 Right-click the CODES column header. Click Add log box appears. Click OK.

pril transactions ep is to isolate ing the transaction

acceptable

, select Data » og box appears.

o appears, select Do lick OK.

Tutorial.book Page 54 Wednesday, July 5, 2006 1:42 PM

category of C, but it has a field length of 7.

You must create a computed field in the Trans_April table that trims the CODES field and has a Length of 4. You can then use this new computed field as the key field for relating Unacceptable_Codes and Trans_April.

Create a computed field in Trans_April

1 In the Table Layout window, click the Add a New Expression button.

2 Enter Codes_Sub_April as the name.

3 Click f(x). The Expression Builder appears.

4 In the Expression box, enter:

SUBSTR(ALLTRIM(CODES),1,4)

5 Click OK.

6 Click the green checkmark. Close the Table Layout window.

Now you can add the Codes_Sub_April field to the view.

Columns. The Add Columns dia Double-click Codes_Sub_April.

Next, determine whether any of the A have unacceptable codes. Your first st unacceptable transactions by compar codes in both tables.

Use the Relations command to find un transactions

1 With the Trans_April table active Relate Tables. The Relations dial

If the Getting Started message als not show this dialog again and c

2 Click Add Table.

Determine which transactions are unacceptable

n so that you can see the field

odes_Sub_Unacceptable.

s_Sub_Unacceptable field des table in the view, the le codes display blanks in the field.

lay unacceptable

ceptable transactions

y blank entry in the ble field. Select Quick Filter »

f the records that have blank b_Unacceptable field. Now t have unacceptable codes

_April and the CODES fields

ecords. Click OK. There are sactions.

ords to a new table.

Tutorial.book Page 55 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

3 Double-click Unacceptable_Codes. The Unacceptable_Codes table appears in the Relations dialog box.

4 Click and drag Codes_Sub_April from the Trans_April table to Codes_Sub_Unacceptable in the Unacceptable_Codes table.

If you link the wrong fields, right-click the line and select Edit Relation. Select the fields that you want to link, and click OK.

5 Click Finish.

Next, you will add the Codes_Sub_Unacceptable field from the Unacceptable_Codes table to the view.

6 Right-click the Codes_Sub_April column header. Click Add Columns. The Add Columns dialog box appears.

7 In the From Table drop-down list, select Unacceptable Codes. The fields in the Unacceptable_Codes table appear in the Available Fields list.

8 Expand the Name colum names. Double-click Unacceptable_Codes.C

9 Click OK.

Now that you have the Code from the Unacceptable_Co records without unacceptab Codes_Sub_Unacceptable

You can apply a filter to disp transactions only.

Apply a filter to display unac

1 Select and right-click an Codes_Sub_Unaccepta Not Equal.

You have filtered out all o entries in the Codes_Su ACL displays records tha only.

2 Remove the Codes_Sub from the view.

3 Select Analyze » Count R 7/281 unacceptable tran

Next, you extract the rec

To arrange the tables within the Relations dialog box, drag the tables with your mouse or right-click and select Arrange Tables.

Chapter 6: Analyze transactions

56 ACL in Practice

Extract the records to a new table s me transactions do ine which can also identify

yees belong.

eral tables and s to report:

ose cards were used for

sed for unacceptable

were used for

Tutorial.book Page 56 Wednesday, July 5, 2006 1:42 PM

1 Select Data » Extract Data. The Extract dialog box appears.

2 In the Extract dialog box, select Record. In the To field, enter Trans_April_Unacceptable. Click OK. Click Yes when you are asked if you want to save the changes to Trans_April.

The new table appears in the view. Adjust the column widths as necessary and rearrange the columns as desired.

Use the Count command to count how many unacceptable transactions there are, in order to write this number to the log and create an audit trail. You should have seven unacceptable transactions.

By looking at the results, you can see that four unacceptable transactions took place on April 14.

You can create a date filter and count the number of records to write this information to the log.

3 Close the view and click Yes when ACL prompts you to save.

■ Identify the employee Now that you have confirmed that so have unacceptable codes, you determ employees made these purchases. You the department to which these emplo

You can obtain information from sev combine it in a single view. The goal i

■ Employee name

■ Credit card number

■ Department

■ Transaction amount

■ Transaction date

■ Transaction code

■ Transaction code description

Tasks

Determine the department of employees wh unacceptable transactions.

Create a list of employees whose cards were u transactions.

Create a report of the employees whose cards unacceptable transactions.

Identify the employees

on Builder appears. Enter :

t_Code),1,1)

n checkmark.

indow.

ted field called an now add to the view.

to the view

de column header. Select

e_1. Click OK.

ode_1 column matches the ode column.

t_Code column because the vant information.

de column and select ns. Click Remove.

Yes when prompted to save.

field in the Employees table employee number field,

Tutorial.book Page 57 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Identify the employees’ departments Your first step is to associate each employee with his or her department.

■ The Company_Dept table contains department codes and names, such as 800000 for Consulting.

■ The Employees table contains the EmpNo field, which begins with the first digit of the department code.

By comparing the first digit of the employee code to the first digit of the company department code, you can identify the department to which the employee belongs.

To make this comparison, you create a computed field in each table that takes the first digit of the Dept_Code values. You then compare this digit to the first digit of the EmpNo in the Employees table.

You start by checking the data types of these fields on the Edit Fields/Expressions tab of the Table Layout window.

Create a computed field that contains the first digit of the Dept_Code field

1 Open the Company_Dept table.

2 Select Edit » Table Layout. The Table Layout window appears. On the Edit Fields/Expressions tab, ensure that the Dept_Code field has a data type of ASCII. Change the type if it is incorrect.

3 On the Edit Fields/Expressions tab, click the Add a New Expression button.

4 Enter the name Dept_Code_1.

5 Click f(x). The Expressi the following expression

SUBSTR(ALLTRIM(Dep

6 Click OK. Click the gree

7 Close the Table Layout w

You now have a new compu Dept_Code_1, which you c

Add the new computed field

1 Right-click the Dept_Co Add Columns.

2 Double-click Dept_Cod

The digit in the Dept_C first digit of the Dept_C

You can remove the Dep new column has the rele

3 Right-click the Dept_Co Remove Selected Colum

4 Close the view and click

Next, you create a computed that uses the first digit of the EmpNo.

Chapter 6: Analyze transactions

58 ACL in Practice

Create a computed field that displays the first digit of the

Name column to

header and select s dialog box

t, select e ld. Click OK.

the view. You are . Your next task will

Tutorial.book Page 58 Wednesday, July 5, 2006 1:42 PM

EmpNo field

1 Open the Employees table.

2 Select Edit » Table Layout. The Table Layout window appears.

3 On the Edit Fields/Expressions tab, click the Add a New Expression button.

4 Enter the name EmpNo_1.

5 Click f(x). The Expression Builder appears. In the Expression box, enter:

SUBSTR(ALLTRIM(EmpNo),1,1)

6 Click OK. Click the green checkmark.

7 Close the Table Layout window.

Now add this field to the view.

8 Right-click the EmpNo column header. Select Add Columns. The Add Columns dialog box appears. Double-click EmpNo_1. Click OK.

The new EmpNo_1 column now appears in the view.

Now relate the Employees table to the Company_Dept table, using the EmpNo_1 field and the Dept_Code_1 field.

Relate the Employees and Company_Dept tables

1 With the Employees table active, select Data » Relate Tables. The Relations dialog box appears.

2 Click Add Table. The Add Table dialog box appears.

3 Double-click Company_Dept.

4 Drag EmpNo_1 in the Employees table to the Dept_Code_1 field in the Company_Dept table.

5 Click Finish.

Your next step is to add the Dept_ the view.

6 Right-click the CardNum column Add Columns. The Add Column appears.

7 In the From Table drop-down lis Company_Dept. Double-click th Company_Dept.Dept_Name fie

The Dept_Name column appears in close to your goal of creating a report be to extract this view to a new table.

Identify the employees

mployees_Dept table to le table using the card

ns command, you must er fields so that you can use ard number fields in the tables.

rd number fields

t table active, select Edit »

hat the CardNum field has tegory of C for character.

dow.

acceptable table.

ield length of 19, which ns extra spaces. Therefore, mber field.

ession button.

he Name.

xpression:

um),1,16)

dow.

Tutorial.book Page 59 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Extract fields to a new table

1 With the Employees table active, select Data » Extract Data. The Extract dialog box appears.

2 Select Fields. Click the Extract Fields button.

The Selected Fields dialog box appears.

3 Add the following fields to the Selected Fields list:

■ First_Name

■ Last_Name

■ CardNum

4 In the From Table drop-down list, select Company_Dept.

The fields in the Company_Dept table appear.

5 Add Company_Dept.Dept_Name to the Selected Fields list.

6 Click OK.

7 In the To field, enter Employees_Dept. Click OK. Click Yes when ACL prompts you to save.

The new Employees_Dept table opens in the view.

Create a list of employees Your next step is to relate the E the Trans_April_Unacceptab number field in each table.

Before you can use the Relatio harmonize the two card numb them as key fields. Check the c Table Layout window for both

Compare and harmonize the ca

1 With the Employees_Dep Table Layout.

The list of fields indicates t a field length of 16 and a ca

2 Close the Table Layout win

3 Open the Trans_April_Un

4 Select Edit » Table Layout

The CardNum field has a f means that this field contai you must trim this card nu

5 Click the Add a New Expr

6 Enter CardNumTrim as t

7 In the f(x) box, enter this e

SUBSTR(ALLTRIM(CardN

8 Click the green checkmark

9 Close the Table Layout win

Chapter 6: Analyze transactions

ACL in Practice

t_Name

ew so that they appear in

able

loyees whose credit cards ble transactions. You can as necessary so that the

immediately that the le for the unacceptable ble someone else used their can create a report of the etaphor Corporation can

Tutorial.book Page 60 Wednesday, July 5, 2006 1:42 PM

Create a detailed view of the unacceptable transactions

1 With the Trans_April_Unacceptable table active, select Data » Relate Tables.

Trans_April_Unacceptable already has a relation with Unacceptable_Codes.

2 Select Add Table. The Add Table dialog box appears. Double-click Employees_Dept.

3 Drag CardNum from the Employees_Dept table to CardNumTrim in the Trans_April_Unacceptable table.

4 Click Finish.

Three tables, Trans_April_Unacceptable, Unacceptable_Codes, and Employees_Dept, are now related.

In the view, you now add all of the columns that you want to include in the final report.

5 Right-click the CardNum column header. Select Add Columns. The Add Columns dialog box appears.

6 In the From Table drop-down list, select Employees_Dept. Add these fields to the Selected Fields list:

■ Employees_Dept.First_Name

■ Employees_Dept.Last_Name

■ Employees_Dept.Dep

Click OK.

7 Arrange the fields in the vi the following order:

■ First_Name

■ Last_Name

■ Dept_Name

■ AMOUNT

■ DESCRIPTION

■ TRANS_DATE

■ CARDNUM

■ Codes_Sub_Unaccept

■ CUSTNO

You can now see the list of emp were used to charge unaccepta rearrange and adjust the fields view is attractive.

Of course, you cannot assume employees listed are responsib transactions because it is possi credit cards. Nevertheless, you results, and then the staff at M investigate the matter further.

Identify the employees

r pr_2003. Click OK.

te the folder C:\ACL nd double-click _2003.html. The report r.

Tutorial.book Page 61 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Report your findings ACL provides two ways to create a report. You can generate a standard ACL report, or you can create a report that you can view in Crystal Reports®. In this procedure, you use ACL’s standard report capability.

Create a standard ACL report

1 Select Data » Report.

The Report dialog box appears.

2 Enter a descriptive Header, such as:

Unacceptable Transactions at Metaphor Corporation in April 2003

3 On the Output tab, select Screen. Click OK.

The report appears on the Do tab.

Next, you save the report as an HTML file.

4 Select Data » Report.

5 On the Output tab, select File. Select HTML Text File from the File Type drop-down list.

6 In the Name text box, ente Unacceptable_Trans_A

7 In Windows Explorer, loca Data\Sample Data Files a Unacceptable_Trans_Apr opens in your Web browse

To learn more about using Crystal Reports to generate a report, select Help » Index and look up “Crystal Reports”.

Chapter 6: Analyze transactions

62 ACL in Practice

following tasks:

ain acceptable and ory Codes to ensure

tables.

able transactions

de unacceptable departments.

a detailed e transactions in

Tutorial.book Page 62 Wednesday, July 5, 2006 1:42 PM

ercises 1 Create a report that lists all acceptable transactions.

The procedures will be the same as for the report that lists unacceptable transactions, except you will use the Acceptable_Codes list as the basis for comparison.

Using Relations, create a view that displays the following information:

■ Employee name

■ Credit card number

■ Department

■ Acceptable transaction amount

■ Transaction date

■ Transaction code

■ Transaction code description

Extract this view to a new table Acceptable_Trans_by_Emp_April, and export the new table to an Excel file.

2 Remember that some transactions have neither acceptable nor unacceptable codes. These are the transactions that require approval, but are not forbidden. Create a table of transactions that are neither acceptable nor unacceptable. How many transactions are in this category?

3 Extract all of the transactions that exist in the approval-required category to another table.

4 Refer to the Trans_April, Trans_April_Unacceptable, and Employees tables. What percentage of employees with credit cards made unacceptable transactions?

5 How would you use ACL to e-mail the results of your report?

■ Chapter review In this chapter you accomplished the

■ Examined the tables that cont unacceptable Merchant Categ that no codes are listed in both

■ Created a table with unaccept only.

■ Linked the employees who ma transactions to the employees’

■ Created a report that provides description of all unacceptabl April.

CHAPTER 7

ACT DATA 7 n be analyzed

ta to a project, 2003 credit

s. the data is not tional steps to repair the

In this c

Organize folders

Expand y May tran

Combine single tab

Correct ca employee

Tutorial.book Page 63 Wednesday, July 5, 2006 1:42 PM

VALIDATE, CORRECT, AND EXTR Repair your data so it ca

So far, you have planned a project, created a project, added da and analyzed it. You based your analysis on Metaphor’s April card transactions.

Now you have been asked to analyze the May 2003 transaction Unfortunately, these transactions are in two separate files, and as neat as the April 2003 transactions. You must perform addi get the data into a state that you can analyze readily. Once you transaction data, you extract the data to new tables.

hapter…

your project with

our project to include sactions

May transactions into a le

rd number and lists

Chapter 7: Validate, correct, and extract data

64 ACL in Practice

■ ct ill move your April

n set up another ork can then be

will not be cluttered

r appears. Enter the . Press ENTER.

the April 2003 Explorer.

CL. Select File » ay 2003 and press

Tutorial.book Page 64 Wednesday, July 5, 2006 1:42 PM

Import the May transaction data You already have a project for Metaphor analysis in ACL. You created a project that contains all of the April transactions. Now you plan to add May transactions too.

Use folders to organize your proje To keep your project organized, you w work into another folder. You can the folder for your May work. All of the w viewed as needed, but the Overview with items that you are not using.

Create a folder called April 2003

1 Select File » New » Folder. A folde name April 2003 for the folder

2 Select and drag all of the tables to folder, as you would in Windows

3 Click ACL_Tutorial_Metaphor.A New » Folder. Name the folder M ENTER.

Tasks

Create folders for April and May transactions.

Import worksheets from the Trans_May.xls Excel workbook.

Compare layouts of the two transaction tables.

port the May transaction data

n verify all of the fields. You errors.

res for the Trans2_May

Tutorial

Import transaction data for May 2003 The Trans_May.xls workbook contains two worksheets with your transaction data for May 2003. Refer to Chapter 3: “Set up your project” if you are uncertain about how to import data.

Import the Trans_May.xls Excel workbook

1 Right-click the May 2003 folder. Select New » Table.

The Data Definition Wizard appears. Continue through the wizard, and select Trans_May.xls.

2 Because ACL saves the data from each worksheet as a separate table, you need to import Trans_May.xls once for each worksheet. Save the first worksheet as the table Trans1_May. Return to step 1 and save the second worksheet as Trans2_May.

Edit the table layout for both new tables

1 With the Trans1_May table active, select Edit » Table Layout. The Table Layout window appears.

2 Edit the AMOUNT, CARDNUM, and CODES fields:

■ Double-click the AMOUNT field. Select NUMERIC from the Type drop-down list. Enter 2 in the Dec field so that AMOUNT has two decimal places. Select -999999.99 from the Format drop-down list. Click the green checkmark.

■ Double-click the CODES field, change the data type to ASCII, and click the green checkmark.

■ Double-click the CARDNUM field, change the data type to ASCII, and click the green checkmark.

3 Close the Table Layout window.

4 Select Data » Verify, the should have zero validity

5 Repeat the same procedu table.

.book Page 65 Wednesday, July 5, 2006 1:42 PM

ACL in Practice 65

Chapter 7: Validate, correct, and extract data

66 ACL in Practice

Co 5 In the Command Line text box, enter DISPLAY. e layout.

play tabs. The table e inconsistency: in ld length of 16, but field length of 15.

orrecting the layout

NT column and

MOUNT value you his value does not

ely change the at it matches the

ay table. AMOUNT comes after the positions of the T.

ble Layout window . Change Len to 15.

Tutorial.book Page 66 Wednesday, July 5, 2006 1:42 PM

mpare and correct the table layouts Before you can combine your tables, the Start, Type, and Length values for each field in each table must be exactly the same. To compare the tables, you can use DISPLAY on the command line.

Compare the layout of the transaction tables

1 Open the Trans1_May table. Select Window » Show Command Line. The command line appears.

2 In the Command Line text box, enter DISPLAY. Press ENTER. ACL displays the table layout.

3 On the Display tab, click the push-pin button to ensure that ACL displays subsequent command results in addition to the “pinned” results.

4 Open the Trans2_May table.

Press ENTER. ACL displays the tabl

6 Compare the contents of both Dis layouts are identical except for on Trans1_May, AMOUNT has a fie in Trans2_May, AMOUNT has a

You can resolve this problem by c of Trans1_May.

Correct the layout of Trans1_May

1 Open Trans1_May.

2 In the view, right-click the AMOU select Quick Sort Descending.

The view shows that the highest A have in Trans1_May is 1983.04. T require 16 characters. You can saf AMOUNT field length to 15 so th AMOUNT field in the Trans2_M starts at position 96, and no field AMOUNT, so you will not affect other fields by changing AMOUN

3 Select Edit » Table Layout. The Ta appears. Double-click AMOUNT Click the green checkmark.

4 Close the Table Layout window.

Most activities in ACL can also be accomplished by typing ACLScript commands on the command line. ACLScript is the language for writing scripts and executing commands in ACL.

Combine and verify the tables

n tables nd method, you can extract le.

rmation in Trans2_May ew table. The new table will ation from both tables.

s to combine the data, but thod is ideal for tables with

method to combine two tables

ble active, select Data »

act Fields. The Selected s.

ist, click the Start column ields are listed in sequential DNUM.

. You are extracting all of the

in the To field. Click OK.

Trans_May_All, which rans1_May.

_May data to the

ct Data » Extract Data.

act Fields. The Selected s.

ist, click the Start column ields are listed in sequential

Tutorial.book Page 67 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

■ Combine and verify the tables In this section, you combine the transactions from both May tables into one table.

You can combine information from two or more tables by using the following methods:

■ Join – The Join command creates a new table that includes selected fields from both files.

■ Relations – The Relations command does not create a new table, but the fields in each of the related tables can be analyzed as if they were in the same table.

■ Extract-and-append – You can extract records and append them to an existing table.

■ Merge – You can merge fields from two tables into one table.

You have two credit card transaction tables, Trans1_May and Trans2_May, with exactly the same layout. To analyze them as one unit, you combine them.

Combine the May transactio Using the extract-and-appe Trans1_May into a new tab

You can then extract the info and add it to the end of the n contain the combined inform

You could use other method the extract-and-append me identical layouts.

Use the extract-and-append

1 With the Trans1_May ta Extract Data.

2 Select Fields. Click Extr Fields dialog box appear

3 In the Available Fields l header so that all of the f order starting with CAR

4 Click Add All. Click OK fields in the order listed.

5 Enter Trans_May_All

ACL creates a new table, contains the data from T

Now append the Trans2 Trans_May_All table.

6 Open Trans2_May. Sele

7 Select Fields. Click Extr Fields dialog box appear

8 In the Available Fields l header so that all of the f

Tasks

Use extract-and-append to combine the May transaction tables.

Verify the combined table.

Check the table for duplicate transactions.

Chapter 7: Validate, correct, and extract data

68 ACL in Practice

order starting with CARDNUM. This order is exactly

mmands to ensure ed correctly.

l. Click OK.

reen is selected.

ity errors

t Analyze » Count ave 200 records.

May table.

May table. You

ppears to have been

Tutorial.book Page 68 Wednesday, July 5, 2006 1:42 PM

the same as the order that you used for Trans1_May.

9 Click Add All. Click OK.

10 Enter Trans_May_All in the To field.

11 On the More tab, select Append To Existing File.

12 Click OK.

ACL opens Trans_May_All. You can now work with this table in ACL as you would any other table.

Verify the new table Next, you use the Verify and Count co that the two tables have been append

Verify the new table

1 Select Data » Verify.

2 Click Verify Fields. Click Add Al

3 On the Output tab, ensure that Sc Click OK.

You should get the result: 0 data valid detected.

Compare the Count in each table

1 In the Trans_May_All table, selec Records. Click OK. You should h

2 Count the records in the Trans1_

You should have 86.

3 Count the records in the Trans2_ should have 114.

The extract-and-append procedure a successful.

Combine and verify the tables

d on the Trans_May_All nd lowest transaction values.

he CUSTNO field. Classify ld. Are the results significant?

at lists the employee name, ollar value of associated

May. View the file in Internet

Tutorial.book Page 69 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Check the combined table for duplicates Always check for data-entry errors when you combine two tables. Duplicate entries can indicate that someone entered the same transaction twice in the original Excel worksheets.

Duplicate transactions can also indicate fraud. Employees can split credit card transactions to avoid scrutiny. Instead of a $2000 purchase, the employee might have two separate transactions of $1000, for example. Alternatively, merchants might charge twice for the same product or service.

Use the Duplicates command to look for transactions of identical amounts.

Check the Trans_May_All table for duplicates

1 Open the Trans_May_All table.

2 Select Analyze » Look for Duplicates. The Duplicates dialog box appears.

3 Select AMOUNT in the Duplicates On list.

4 Click List Fields. Click Add All. Click OK.

5 On the Output tab, ensure that Screen is selected. Click OK.

6 View the results. You are checking for duplicate AMOUNT values.

You should have a transaction for a computer software store and one for Southwest in the same amount. You should also have two transactions at the Hilton one day apart in the same amount.

What do you think is the possible significance of these duplicate amounts?

Exercises 1 Run the Profile comman

table. Note the highest a

2 Check for duplicates in t the table on the same fie

3 Export a table to XML th card number, and total d transactions per-card in Explorer.

Chapter 7: Validate, correct, and extract data

70 ACL in Practice

■ mbers in the table u apply a filter to the nction that consists

ng blanks from each

h card number, s exactly 16 digits.

bers

tive, click the Edit

ppears.

ollowing expression

tes that the filter UM value consists

ou can see, 195/200 means that 5

o the beginning of

isplay the invalid

Tutorial.book Page 70 Wednesday, July 5, 2006 1:42 PM

Isolate and correct validity errors

In the last section, you verified the data that you imported. However, verifying the data is different from validating it. For example, you determined that the CARDNUM field consisted only of characters, so that the data is ASCII text. However, you still must determine whether the card numbers are valid, which means that each entry consists of 16 digits and no other characters.

In this section, you create:

■ A filter to display only the valid entries.

■ A filter that displays only the invalid entries.

If some entries are invalid, you can either correct the problems, or filter out the records that contain those entries.

Check the format of card numbers You need to determine which card nu are in an invalid format. To do this, yo CARDNUM field that uses a nested fu of ALLTRIM( ) and MAP( ):

■ ALLTRIM( ) removes all traili card number entry.

■ MAP( ) evaluates whether eac trimmed of trailing blanks, ha

Apply a filter to show invalid card num

1 With the Trans_May_All table ac View Filter button.

The Edit View Filter dialog box a

2 In the Expression box, enter the f all on one line:

MAP(ALLTRIM(CARDNUM), "9999999999999999")

The string of sixteen nines indica determines whether each CARDN of sixteen digits.

3 Click OK. ACL applies the filter.

4 Count the number of records. As y records remain in the view, which records do not meet the criteria.

5 In the Filter text box, add NOT t the expression:

NOT MAP(ALLTRIM(CARDNUM), "9999999999999999")

Press ENTER. NOT causes ACL to d entries.

Tasks

Determine whether all the card numbers have exactly 16 digits.

Make a 16-digit credit card field.

Extract repaired card number records to a new file.

Correct the new list of employees.

Isolate and correct validity errors

er field d numbers to another list of ntain dashes and spaces. To eld in Trans_May_All in rs are sixteen digits long with

st also contain all of the valid ging them. Invalid card without altering the digits.

corrected card numbers

able, click the Remove Filter filter. The view now displays

ut. The Table Layout

pression button.

in the Name field.

e Expression Builder that you create will populate d.

ession:

RDNUM,"0123456789"))

s/Expressions tab appears

rk.

indow.

ld to the view.

Tutorial.book Page 71 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

6 Count the records. You should have 5/200 visible.

The invalid card numbers contain either extraneous dashes or spaces. In this case, you can see all of the invalid entries at a glance, but in a large project, you might not be able to do so.

Create a 16-digit card numb Now you compare these car card numbers that do not co do this, create a computed fi which all of the card numbe no spaces or dashes.

The field that you create mu card numbers without chan numbers must be corrected

Create a computed field with

1 In the Trans_May_All t button to remove the

all of the records.

1 Select Edit » Table Layo window appears.

2 Click the Add a New Ex

3 Enter CCN_Corrected

4 Click the f(x) button. Th appears. The expression the CCN_Corrected fiel

5 Enter the following expr

ALLTRIM(INCLUDE(CA

6 Click OK. The Edit Field again.

7 Click the green checkma

8 Close the Table Layout w

You can now add the new fie

Chapter 7: Validate, correct, and extract data

72 ACL in Practice

Add the CCN_Corrected computed field to the view new table contains only valid ract the records to a for further

table

elds button. The s.

M contains invalid e that field in your rom the Selected

me ed. Click OK.

orrected.

Tutorial.book Page 72 Wednesday, July 5, 2006 1:42 PM

1 Right-click the CARDNUM header. Select Add Columns. The Add Columns dialog box appears.

2 Double-click CCN_Corrected. Click OK.

3 Remove the CARDNUM column.

Because you now have the corrected card number field, you no longer need to see the CARDNUM field.

Now you apply a filter to ensure that the CCN_Corrected field contains only digits.

4 In the Filter text box, enter:

MAP(CCN_Corrected,"9999999999999999")

Press ENTER.

5 Count the number of records. All 200 are visible.

6 Click the Remove Filter button to remove the filter.

Your next step is to extract this information to a new table.

Extract the corrected records to a Now that you have created a field that card numbers, your next step is to ext new table. You then use this new table analysis.

Extract the corrected records to a new

1 Select Data » Extract Data.

2 Select Fields. Click the Extract Fi Selected Fields dialog box appear

3 Click Add All. Because CARDNU entries, you do not want to includ new table. Remove CARDNUM f Fields list. Click OK.

4 In the To text box, enter the file na Trans_May_All_CCN_Correct

ACL opens Trans_May_All_CCN_C

Isolate and correct validity errors

ee record table n a slightly different list of s_May_with_CCN.xls. The ployees who have credit

oyees

_with_CCN.xls into the e it as _CCN.

able.

ecord has “End of File” in the ll the other field entries for

ly, this is not an employee red.

ay_with_CCN table active, le entry.

t Equal.

tered out.

to another table, then you do serving the filter on the

new table

ta.

ck Extract Fields. Click Add

the filename ected. Click OK.

Tutorial.book Page 73 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Export the combined transaction table to Excel You have created and checked a table that combines the two transaction tables that represent May 2003 credit card transactions. You might want to have this data in Excel format as well.

Export the Trans_May_All_CCN_Corrected table to Excel

1 With the Trans_May_All_CCN_Corrected table active, select Data » Export to Other Application.

2 Select Fields.

3 Select the fields you want to export in the order you want them to appear. The fields will appear in the Excel worksheet in this order, from left to right.

4 From the Export As drop-down list, select Excel.

5 In the To field enter the filename Trans_May_All_Corrected. Click OK.

You can also select the path for the file. If you enter just the filename, the new file will be saved to the last folder that you used to store your ACL data.

Create an error-free employ For May, you have been give employees to use: Employee table includes only those em cards with Metaphor.

Import the new table of empl

1 Import Employees_May May 2003 folder and sav Employees_May_with

2 Scroll through this new t

You can see that the bottom r First_Name field, and that a this record are empty. Clear record, and it should be filte

Filter invalid employee recor

1 With the Employees_M right-click the End of Fi

2 Select Quick Filter » No

The invalid record is now fil

If you extract the valid data not have to worry about pre original table.

Extract the valid records to a

1 Select Data » Extract Da

2 Select Fields button. Cli All. Click OK.

3 In the To text box, enter Employees_May_Corr

Chapter 7: Validate, correct, and extract data

74 ACL in Practice

Ex sis ugh several using ACL. What h the data?

ely come up with your own work.

ceptable Merchant

des that are used sibilities?

repeated ls with the same

ral transactions 72 hours) with the

sactions occurring

sactions have , $200, or $450?

to a card number, for these credits?

come close to or

ase in the

or charging other

pent the most? To rge their expenses?

Tutorial.book Page 74 Wednesday, July 5, 2006 1:42 PM

ercises 1 Suppose you wanted all card numbers to have the

form: xxxx-xxxx-xxxx-xxxx. What strategy would you follow to create a computed field like that?

2 If you had the information available, checking the Social Security Number (SSN) for each employee would help determine whether each record was unique. How would you check for duplicate Social Security Numbers? How would you ensure that all Social Security Numbers were in the format of one long string of digits: xxxxxxxxx?

3 Devise a strategy for analyzing your May transactions that closely mirrors the strategy you followed for the April transactions. Find the unacceptable transactions for this set of transactions.

4 For your own projects, think of various approaches that improve the quality of your data before you import it into ACL. Think of ways to test your data after you import it into ACL.

■ Ideas for further analy In this tutorial, you have worked thro examples of how to perform analyses other analyses could you perform wit

Consider these questions. You can lik numerous ideas that are applicable to

■ Do the transactions have unac Category Codes?

■ Do the Merchant Category Co match the cardholder’s respon

■ Does the cardholder conduct transactions at regular interva merchant?

■ Does the cardholder have seve within a brief time period (say same merchant?

■ Does the cardholder have tran on weekends or holidays?

■ Do an unusual number of tran rounded values, such as $1000

■ Are there credits charged back and what are the explanations

■ Does the cardholder regularly exceed his or her credit limit?

■ Has there been a sudden incre cardholder’s purchases?

■ Is the cardholder responsible f people’s expenses?

■ Which five or ten employees s which customers did they cha

Chapter review

ished the following tasks:

orkbook with two

on from two tables into one

that included invalid data.

ansactions table to Excel.

filtering out an invalid record sults to a new table.

ombines all of the CC table.

Tutorial.book Page 75 Wednesday, July 5, 2006 1:42 PM

ACL in Practice

Is there any relation between spending a great deal and charging unacceptable transactions?

■ Determine the total spending by department on a monthly basis. Use the DeptCode field in the Employees table.

■ If an employee has charged air fare, what other travel-related expenses has he or she charged? For example, if an employee charges air fare, he or she is likely to charge a hotel room and transportation. Is this the case? What are some possible explanations for the patterns that appear?

■ If the codes for caterers and bakeries were unacceptable, how much would money have been saved?

■ Chapter review In this chapter you accompl

■ Imported an Excel w worksheets.

■ Combined informati table.

■ Corrected a data field

■ Extracted the new tr

■ Corrected a table by and extracting the re

■ Created a table that c transactions into one

Chapter 7: Validate, correct, and extract data

76 ACL in Practice

Tutorial.book Page 76 Wednesday, July 5, 2006 1:42 PM

INDEX

mes 25

iew 52, 54, 58, 72 rt order in view 10, 15

rom view 55

ts ails 13

6 6 13, 69 3

0, 54, 58, 60 single fields 11 lds to include 13

ed-value files

Tutorial

A Absolute value

Profile command results 12 Acceptable_Codes.mdb

data file 5 Access. See Microsoft Access data Account number categories

Metaphor Corporation 4 Allowable expenses

Metaphor Corporation 4 ALLTRIM( ) function

using 43, 45, 52, 54, 57, 59, 70 Analysis strategy

planning 7 Analytical strategy

planning 6 Append option

Extract command 67 Applications

exporting to other 35 Ascending

Quick Sort order 15 ASCII

data type 27 ASCII data

formatting in expressions 36 Audit trail

leaving 53 log tab 15

B Backup copy

ACL project 26 BETWEEN( ) function

using 43 Blank entries

removing 55 Blanks

removing leading and trailing 45, 70 Bonuses

finding total 15

C Calculated fields. See Computed fields Categories

data type 28 expense, Metaphor Corporation 4

Cells blank 55

Character data applications 27 formatting in expressions 36 numerals in 24

Characteristics of data checking 27

Checkmark green 17

Classify command using 46

Code categories Metaphor Corporation 5

Codes acceptable 5 common 51 comparing 54, 57

Column titles changing 25 and field na

Column width changing in

Columns adding to v changing so removing f

Combining data methods 67

Command defined 15

Command line using 66

Command resul viewing det

Commands Classify 46 Count 11, 1 DISPLAY 6 Duplicates Export 35, 7 Extract 56, Join 50 Profile 12 Relations 5 running on selecting fie Statistics 11 Summarize Total 15, 34 Verify 68

Comma-separat

.book Page 77 Wednesday, July 5, 2006 1:42 PM

Ind

D Da

data

ration 4

Tutorial.book Page 78 Wednesday, July 5, 2006 1:42 PM

importing 25 mpany_Departments.txt

data file 5 mputed fields

adding to view 57 creating 17, 52, 54

nditions multiple 38

unt command using 11

UNT field Summarize command 42

edit card policies Metaphor Corporation 4

edit cards finding expiry date 34

edit_Cards_Metaphor.xls data file 5

edit_Cards_Metaphor.xls worksheet importing 23

V files importing 25

cle of analysis typical 6

ta appending 67 combining 50 errors

eliminating 7 exporting 35 filtering 72 format information 7 importing through the wizard 22 integrity testing 13, 27 isolating specified portions 43 modifying with computed fields 28

samples for testing 7 sources 6 validity errors, finding 29 verifying type 29 viewing in original format 6

Data Definition Wizard using 22

Data definitions modifying 28

Data files opening 10 tutorial 5

Data format checking 27

Data type changing 24, 35 checking 51, 53, 66 verifying 27

Data type categories Table Layout window 28

Data-entry errors finding 69

Date data Statistics command 11

Dates formatting in expressions 16 selecting in Expression Builder 34

Decimals rounding 17

Default view displaying 17

Delimited files importing 23

Descending Quick Sort order 15

Digits using as character data 24

DISPLAY command using 66

Documentation project 30

Double entries finding 13

Drill-down filter command results 13

Duplicate records finding 13

Duplicates command using 13, 69

E Edit Field Properties scree

using 24 Edit view filter

dialog box 16, 17, 34 Employee numbers

finding duplicates 13 Employees.cvs

data file 5 Empty cells

removing 55 End of file record

deleting 73 Entries

duplicate 13, 55, 69 invalid, displaying 70

Errors data validity 29 data-entry 7, 69 testing for 65 validity 68

Excel. See Microsoft Excel Expenses

allowable Metaphor Corpo

isolating 43, 45 summarizing by code

Index

) 43, 45, 57, 70 ( ) 43

) 71

G( ) 52, 54, 57

k ges 17

1, 53, 59

ld names 24

order 10 der 15

at 61

esults 13

ing 69 ding 13

Tutorial

Expiry dates finding 34

Export using Excel format 73

Export command using 35, 73

Expression defined 16

Expression Builder See also Edit view filter functions list 35 opening 52 using 17

Expressions checking syntax 36 creating and saving 17 formatting

character data 36 multiple conditions in 38

Extract command append option 67 using 44, 56, 58

F Field length

changing 66 checking 51, 53, 59

Field names and column titles 25 importing from data file 24

Field order changing in the view 10 selecting for export 73

Field separators specifying 24

Field totals finding 34

Field type. See Data type

Field width adjusting in view 17

Fields adding to view 52, 54, 58 blank entries in 55 changing order 60 combining from multiple tables 50 computed 28, 52, 54 editing 28 eliminating invalid characters 70 harmonizing 51, 53, 59 key 51, 66 ordering sequentially 67 removing from view 55 sort order 15, 37 verifying content 27

Files importing 23 Metaphor data 5

Filters applying multiple 45 creating 16, 34, 72 removing 13, 17 saving 37 setting multiple conditions 38

FIND( ) function using 35

Folders creating 64

Format data

checking 27 data file 6 date expressions 16 expression

verifying 36 invalid 70

Formats export 35

Functions ALLTRIM( BETWEEN FIND( ) 35 INCLUDE( MAP( ) 70 MATCH( ) nesting 70 SUBSTRIN using 35

G Green checkmar

saving chan

H Harmonization

key fields 5 Header rows

using as fie Headings

and column and sort or

History project 30

HTML report form

Hyperlink command r

I Identical

entries, find records, fin

Import CSV files 25

.book Page 79 Wednesday, July 5, 2006 1:42 PM

Ind

Int

Inv

J Joi

K Ke

L Len

Lin

12 1

ing 15, 37 0

Tutorial.book Page 80 Wednesday, July 5, 2006 1:42 PM

Excel worksheets 23, 65 Microsoft Access data 25 using the wizard 22

CLUDE( ) function using 71

actness of data testing 13

egrity of data verifying 27

alid characters eliminating 70

alid formats finding 70

alid records filtering out 73

oices duplicate 69

n command compare with Relations 50 using 52

y fields preparing 51, 53, 59

gth of field checking 51, 53, 59, 66

k See Hyperlink, command results

g tab viewing command entries 15

gical expressions filtering data 16

M MAP( ) function

using 70 MATCH( ) function

isolating data 45 Maximum value

Profile command results 12 MDB files

importing 25 Merchant Category Code list

about 5 Metaphor Corporation

about 3 Methodology

documenting 30 Microsoft Access data

importing 25 Microsoft Excel

data, importing 23, 65 export format 73

Minimum value Profile command results 12

Multiple tables working with 67

N Navigator pane

about 10 Nested functions

using 70 Notes tab

displaying 11 using 30

Number of records counting 11

Numerals using as character data 24

Numeric data alpha characters in 29 Profile command 11, Statistics command 1

O Objectives

tutorial 2 Operators

using in filters 38, 45 OR statement

MATCH( ) function 4 Order

ascending or descend columns in the view 1

Order of fields arranging in view 67

Output fields selecting 13

Overview of data obtaining 11

Overview tab about 10 organizing 64

P Plain text

export format 35 Policies

credit card usage 4, 5 Profile command

using 12 Project methodology

documenting 30 Project Navigator

location 10 Project planning

Index

SUBSTRING( ) function function

mand

ectives 2

ndow ata type 35 ta characteristics 27 7

66 5

columns 52, 54, 58 67

ns dialog box 55 50 Dept 57

5, 73 ecords 56, 58

Tutorial

analysis strategy 7 Projects, ACL

about 10 adding data 23 creating 22 opening 10 organizing 64 saving 26 viewing content 11

Properties of ACL project displaying 11

Push-pin button about 66

Q Quick Filter

using 55 Quick Sort

about 15 using 37

Quotation marks character data 36 date data 16

R Ranges of data

finding 43 Record layout. See Data, format information Record of commands

Log tab 15 Records

counting 11, 68, 72 extracting 44, 56, 58 invalid

filtering out 73 sorting 37

Relations command

compare with Join 50 using 54, 58, 60

Relations dialog box arranging tables 55

Reports creating 61

Results displaying multiple 66 recording 53 saving 37

Reverse quotation marks dates 16

S Save

automatic 26 Save the Open Project

button 26 Screen

ACL described 10

Scripts writing 66

Search for substrings using FIND( ) 36

Separators field 24

Single fields selecting 11

Sort Quick 37

Soundness of data testing 13

Sources data 6

Spaces eliminating 45, 70 trimming 43

Start positions checking 66

Statistics comma using 11

Strings comparing finding 36

SUBSTR( ). See SUBSTRING( )

using 52, 54 Substrings

finding 36 Summarize com

using 42 Summary

tutorial obj

T Table Layout wi

changing d checking da displaying 1

Table layouts comparing editing 28, 6

Tables adding new appending arranging

Relatio combining Company_ creating 23 Employees empty 53 exporting 3 extracting r joining 52

.book Page 81 Wednesday, July 5, 2006 1:42 PM

82 ACL in Practice

Tit

Tra

65 23

Tutorial.book Page 82 Wednesday, July 5, 2006 1:42 PM

apter :

methods of combining 67 moving to new folder 64 opening 11 relating 54, 58, 60 removing blank entries 55 saving 26 Unacceptable_Codes 23

bs displaying multiple results 66

sk sheet sample 30

st data using to refine strategy 7

xt export format 35

xt files importing 23

les of fields importing 24

tal command using 15, 34

tal values Profile command results 12

tals accumulating per value 46

ns_April worksheet importing 23

ns_April.xls data file 5

nsaction totals per credit card 46

nsactions comparing 54 identifying unacceptable 53 isolating by category 43 split 69 summarizing by code 42

pe drop-down list 35

Type of data changing 24 finding 28

U Unacceptable_Codes.txt file

about 5 importing 23

Use first row as field names option 24

V Validate

compare with verify 70 Validity errors

checking 65 Values

comparing 57 finding in multiple tables 50

Verify compare with validate 70

Verify button checking expressions 36

Verify command using 29, 68

Views adding new columns 52, 54, 58 column sort order 15 field order

changing 60 removing

blank entries 55 columns 55

W Web browser

viewing reports 61 Width of fields

adjusting in view 17 Window

Table Layout 17, 35 Worksheets

importing multiple selecting for import

00 6

A C

L S

er vi

ce s

Lt d.