ACL Training Materials

1. ACL Training Materials Unit B: Analysing data validity, Report & Expressions 主要參考資料來源: KPMG ACL課程講義資料 PriceWaterHouseCooper ACL課程講義資料

2. 第四章驗證資料檔案 • Verify 驗證資料 • Count 計算筆數 • Total 加總數值欄位 • Statistics 統計數值 • Profile 剖析資料(被包含於Statistics) • Sequence 順序測試 • Duplicates 重號測試 • Gaps 缺號測試

3. Sec. 4-1 驗證資料(Verifying Data) • 目的: 確保資料內容與指定欄位型態相符 • 選取IFD, 並開啟Default View • 執行[Analyze] / [Verify] • 選取要驗證的欄位項目[Sequence on] • 選取要顯示的欄位項目[List Fields] • 註: 要選取多個欄位時, 先按住(Ctrl), 再點選

4. Analysing data validity • Sequence • Gaps • Duplicates • Verify

5. Verify • Verify • Returns all fields with invalid data - ie the data in the field does not match the data type defined in the Input File Definition; • The presence of data validity errors may suggest poor data entry rules, poor database validity controls, or data corruption;

6. Analysing data validity • Verify • In a large data file, in order to be sure all errors are noted, the Error Limit should be set to a larger number than ACL’s default setting of 10.

7. Analysing data validity

8. Sequence, Gaps, Duplicates • Sequence - 可偵測資料Sort 之錯誤 • Gaps - 係測試資料是否缺號，可選擇以區間表達或單筆列示, • 且僅可執行數值欄位 • Ex:A12345 與 B12346沒缺號 • Duplicates -係測試資料是否重覆 • The file should be sorted on the desired field before these commands are run. • The Sequence command is capable of performing all three commands simultaneously if the appropriate boxes are checked; The Gaps can be returned as individual missing values or as a range of missing values

9. Sequence, Gaps, Duplicates • 查核應用：可以用來檢核銷貨發票號碼是否有重覆或缺號之情形。

10. 第五章資料瀏覽與報表製作(View & Report)

11. Accessing ACL Commands • Report ： • 產出報表 • - 設定符合的條件值。 • - 設定產出的格式。

12. Report • Main 主頁 1.可設定產出報表的“頁首/頁尾” 2.定義欲output的資料條件。

13. Report • 輸出的方式： • -Screen • -Graph • -File(.Text或 .Html檔案) • -Print

14. Accessing ACL Commands • The Data Menu contains commands used to manipulate data by changing data within the current Input File or extracting data to create a new Input File 將資料匯到ACL的另一個資料視窗Data View window 將資料匯出ACL(亦可選擇匯出的格式(ex：.txt或.xls)

15. 第六章定義計算公式(Expression & Filter)

16. Using expressions • Expressions can be added to any ACL command, within the expression field • Expressions can by typed in manually, or ‘built’ using the expression builder To access the expression builder, click on the ‘If’ button PricewaterhouseCoopers

17. Using expressions PricewaterhouseCoopers

18. Using expressions PricewaterhouseCoopers

19. Using expressions PricewaterhouseCoopers

20. Using expressions • Eg Total command: • Total all payables in a payables file • Total all payables in a payables file with due date in next month • Total all payables in a payables file with due date in next month, for a specific vendor only • Expressions are an important part of creating and executing a command in ACL as without them only the data set as a whole can be examined PricewaterhouseCoopers

21. Using expressions -小數位數 小數位數:ACL小數表達係取決於所設定之公式為小數點幾位數 範例: 1.7/3=2 2.7.000/3.00=2.333 3.1.1*1.1=1.2 4.1.10*1.10=1.21 5.100,000*(0.12/365)=0 6.100,000*0.12/365=32.88 PricewaterhouseCoopers

22. Using expressions • 數字無須加千分位符號,最多僅有22個運算位元 • ASCII : • 一定要加上單引號(‘’)或雙引號(“”) • 日期 • 1.記得加單引號(`981231`) • 2.僅能使用`YYYYMMDD`或 `YYMMDD`格式 PricewaterhouseCoopers

23. Using expressions • We only want included in the command those goods with a cost price over $10 • See if you can create the relevant expression PricewaterhouseCoopers

24. Using expressions We select the field over which we want to set the condition, in this case ‘cost price’ PricewaterhouseCoopers

25. Using expressions We select the operator in which we are interested PricewaterhouseCoopers

26. Using expressions We enter in the appropriate value, in this case 10 PricewaterhouseCoopers

27. Using expressions We select the field over which we want to set the condition, in this case ‘cost price’ We select the operator in which we are interested We enter in the appropriate value, in this case 10 PricewaterhouseCoopers

28. Using expressions PricewaterhouseCoopers

29. Using expressions • Expressions in ACL fall into one of four catagories: • Numeric - uses mathematical operators such as + or -. Usually applied to numeric fields; eg (sales price - cost price) * 0.1 • Character - uses operators acting upon character fields. Mathematical operators cannot be used; eg voucher type = ‘IN’ • Logical - uses logical operators such as < or >. Depending on the type of operators, can be used on both numeric and character fields; eg cost price > 10 • Date - uses dates in its calculations. eg date_billed > `20010402` PricewaterhouseCoopers

30. Using expressions • The data type of the field(s) contained within the expression determines what sort of operators (and functions) can be used in the expression • An expression can be checked for validity before it is executed, using the Verify button PricewaterhouseCoopers

31. Using expressions • Using the AND and OR operators, an expression can have several different statements built into it: eg cost_price > 10 AND quantity_on_hand < 4000 eg voucher_type = ‘IN’ OR value > 0 • Functions within expressions can add another level of complexity: eg cost_price > 10 AND quantity_on_hand < 4000 AND SUBSTR(item_id , 1 , 2 ) = ‘80’ PricewaterhouseCoopers

32. Workshop B-1 • 任務： • 1. 開啟AR.fil，並驗證資料完整性。(資料小計) • 2. 請計算 Trans_Amount 總額。 • 3. 驗證欄位〝INVOICE_AMOUNT〞之正確性。(了解是否有負值) • 4. 請找出交易代碼 (Trans_Type) 為“CN”,”IN”之交易。 • 5. 將 4. 之執行結果以報表格式列印在螢幕上， 並請加註表首〝Independency CO.,”； 表尾〝Prepared by XXXX〞。

33. Workshop B-2 自ABC公司下載應收帳款資料(AREC.DBF) 請於ACL中定義AREC.DBF • 有多少筆資料帳單日期(Invoice Date)大於到期日? • 正數金額有幾筆?總金額為何? • 負數金額有幾筆?總金額為何? • 1997年2月份到期，交易別(Type)是IN的筆數有多少?金額為何? • 1997年3月及9月份到期，金額為負數的資料筆數有多少?金額為何?