Scribd
Upload
9 views2 pages

Tutorial 6

This tutorial focuses on organizing and analyzing transaction data for May 2003 by importing, verifying, and validating the data. It includes exercises on creating folders, combining tables using ACLScript, and ensuring data accuracy through various filters and computations. The tutorial aims to enhance skills in data management and analysis within the context of IT auditing.

Uploaded by

op z1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views2 pages

Tutorial 6

This tutorial focuses on organizing and analyzing transaction data for May 2003 by importing, verifying, and validating the data. It includes exercises on creating folders, combining tables using ACLScript, and ensuring data accuracy through various filters and computations. The tutorial aims to enhance skills in data management and analysis within the context of IT auditing.

Uploaded by

op z1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

University Of Bahrain

College of Information Technology


Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Tutorial 6

Tutorial Objectives
By the end of this tutorial you should;
- Be able to join and relate tables based on key fields
- Be able to further analyze your data to deduce decisions and conclusions
-
Exercise 1 – Get Organized
After the previous tutorial, we have completed our analysis our April’s transactions, before we move on to
May, we would like to organize our project before importing new data – this has many advantages such as less
confusion, and future reference.
Exercise 1.1 – Using Folders
1. Select File -> New -> Folder and name it: April 2003 -> click Enter.
2. Select and drag all tables into the new folder -> then create a new folder for May 2003.
Exercise 2 – Analyzing May Transactions
Exercise 2.1 – Import and Verify May Transactions
1. Just as you learned in Tutorial 2, now we will import May’s Transactions from Trans_May.xls. Add
the tables to the folder May 2003 directly and name them Trans1_May and Trans2_May.
2. Edit the Table Layout for both tables ensuring: AMOUNT is Numeric with a format of -999999.99,
and CODES and CARDNUM are ASCII.
3. Ensure that both tables have zero validity errors; verify the data.
Exercise 2.2 – Combining the Tables & Using ACLScript
ACLScript is the language for writing scripts and executing commands in ACL.
1. With Trans1_May active -> select Window -> Show Command Line.
2. In the Command Line -> enter DISPLAY -> pin the display tab.
3. Repeat for Trans2_May -> lock the views to be able to compare the results.
4. Compare both tables and note the difference -> edit the Table Layout to ensure that the DATA
TYPES, LENGTH, and START of each attribute are the same before joining the tables.
5. There are four methods to combine tables;
i. JOIN
ii. RELATIONS
iii. EXTRACT-AND-APPEND – we will use this one
iv. MERGE
6. With Trans1_May, extract the table to a new table called Trans_May_All -> ensure you re-order the
selected fields based on their start position.
7. After extracting Trans1_May -> open Trans2_May -> extract the data the same way you did in the
previous step -> ensure that you select Append To Existing File in the MORE Tab -> click OK.
8. Verify the table to ensure no validity errors -> and count the records.
9. As you have learned earlier, look for duplicates in the table – when combining tables; always look
for duplicates as it may identify fraud or double entry by employees -> set Duplicate on AMOUNT
and select all the fields for LIST FIELDS -> observe and discuss the results.
10. Run the profile -> check for duplicates and then classify on CUSTNO -> note your findings.
11. Just as you learned in Tutorial 2, now we will import May’s Transactions from Trans_May.xls. Add
the tables to the folder May 2003 directly and name them Trans1_May and Trans2_May.
1
Prepared By: Ahmed Ramadan
University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445

Exercise 2.3 – Validating Data


In this exercise, we will validate that the data is actually correct in the tables.
1. With Trans_May_All active -> apply the following filter:
MAP (ALLTRIM (CARDNUM),”9999999999999999”) - > count the records
MAP Command evaluated a field to assess whether it matches the given criteria.
2. Repeat the previous step with the following filter this time:
NOT MAP (ALLTRIM (CARDNUM),”9999999999999999”) - > count the records
3. Now we want to create a computed field with the correct Card Numbers only -> create the new
field called CCN_Corrected using the following formula and add it to the view:
ALLTRIM (INCLUDE (CARDNUM,”0123456789”))
Remove column CARDNUM and then execute the filter: MAP
(CCN_Corrected,"9999999999999999") to validate all your records -> count the records.
4. Extract this new table with the correct data to a name table called: Trans_May_All_CCN_Corrected
5. Export the table as an excel sheet with the attributes arranged the way you want it to be.
Exercise 2.4 – Importing and Filter New Employees List
1. Import the Employees_May_with_CCN.xls to the May 2003 folder as Employees_May_with_CCN
2. Filter the table with the Quick Filter -> Not Equal with the last record -> extract the correct table
to a new table called: Employees_May_Corrected

2
Prepared By: Ahmed Ramadan

Common questions

Powered by AI

Reordering selected fields based on their start position ensures that data attributes are correctly aligned, preserving relational integrity. This alignment prevents misplacement of data across columns, which is critical for accurate data analysis, especially when combining datasets from multiple tables .

ACLScript allows for automated execution of commands, enabling consistent and efficient manipulation and verification of datasets through scripting. It facilitates the comparison of tables, ensures data type consistency, and helps in the detection of duplicates, thus streamlining the data verification process .

The steps for validating card numbers include applying filters to check against standard formats, creating a computed field for corrected numbers, and executing validation filters to confirm completeness. These steps ensure that the dataset contains only valid records, crucial for maintaining data accuracy and reliability in analysis .

Organizing data before importing new datasets minimizes confusion and facilitates future reference by categorizing information into folders. This structure helps to maintain clear, systematic records and simplifies accessing previous analyses for comparisons or audits .

The purpose of creating a computed field like CCN_Corrected is to ensure that the card numbers comply with specific criteria, thus validating data integrity. By trimming and filtering out unwanted characters, only valid card numbers are retained for accurate analysis, reducing errors in the data set .

The extraction and appending technique allows for the addition of data from multiple sources into a single dataset while maintaining data integrity. It ensures that all columns are aligned, minimizes errors during the combination process, and facilitates comprehensive analysis by consolidating relevant data in one place .

When exporting a corrected data table, considerations include ensuring that the data layout matches analytical needs, preserving data formatting for consistency, and verifying that all corrected and relevant data is included. This guarantees that the exported dataset is usable for further analysis or presentation without requiring additional formatting adjustments .

Verifying data layout consistency before combining tables ensures that attributes align correctly, avoiding data integrity issues that could lead to misinterpretation during analysis. Consistent data types, lengths, and starting positions are crucial for accurate data linkage and reliable analytical results .

Identifying duplicates in transaction tables can reveal patterns of repeated entries which may signify fraudulent activities or clerical errors. By setting duplicates on specific fields like AMOUNT, auditors can quickly spot anomalies that warrant further investigation, potentially uncovering fraudulent behavior or systematic entry mistakes .

Filtering during the import process ensures data accuracy by excluding erroneous or unwanted records—in this case, using 'Not Equal' to exclude the last record. It ensures that only the correct and relevant employees’ data is retained, minimizing the risk of incorrect analyses due to inclusion of faulty information .

You might also like