Audimation Services has been acquired by CaseWare International Learn More.

X
Icon


Blog Image

Tech Tip: Identifying Duplicates and Removing Reversals – Revisited


In the Aug-2019 newsletter we reviewed this process using IDEA v9 or v10.

IDEA 11.2 released 13-Oct-2020 updates the analytic, Duplicate Key Exclusion: You can now view output of all the records that are related to the unique duplicate key exclusion.

Download Sample Invoice Data File.

The basic steps are similar, using INVOICE FILE as an example.

  1. Open: INVOICE FILE
    1. Data tab: SORT
      1. Fields:
        1. VENDOR
        2. VENDOR_INVOICE_NO
        3. INVOICE_DATE
      2. Create new file: SORTED INVOICES
  2. Open: SORTED INVOICES
    1. Data tab: APPEND FIELDS
      1. ABS_AMOUNT
        1. Type: Numeric
        2. Size: 2 decimal places
        3. Parameter: @ABS(AMOUNT)
        4. Description: Absolute Amount
      2. PREC_NO
        1. Type: Numeric
        2. Size: 0 decimal places
        3. Parameter: @PRECNO()
        4. Description: Physical Record Number
    2. Analysis tab: DUPLICATE KEY – EXCLUSION
      1. Fields to match (example)
        1. VENDOR
        2. VENDOR_INVOICE_NO
        3. PO_NO
        4. ABS_AMOUNT
      2. OUTPUT ALL DUPLICATE RECORDS (new in IDEA 11.2)
      3. Fields that must be different
        1. AMOUNT
      4. Create new file: INVOICE REVERSALS FOR REVIEW
        Duplicate Key Exclusion Dialog
  3. Open: INVOICE REVERSALS FOR REVIEW
    1. Analysis tab: SUMMARIZATON
      1. Fields to summarize:
        1. VENDOR
        2. VENDOR_INVOICE_NO
        3. PO_NO
      2. Numeric fields to total:
        1. AMOUNT
        2. PREC_NO
      3. Statistics to include:
        1. Sum
        2. Maximum
      4. Create new file: SUM REVERSALS
    2. Analysis tab: JOIN
      1. Primary File: INVOICE REVERSALS FOR REVIEW
      2. Secondary File: SUM REVERSALS
        1. Fields to include:
          1. NO_OF_RECS
          2. AMOUNT_SUM
          3. PRECNO_MAX
      3. Match:
        1. VENDOR
        2. VENDOR_INVOICE_NO
        3. PO_NO
      4. Match Type: MATCHES ONLY
      5. Create new file: INVOICE REVERSALS FOR REVIEW WITH TOTALS
  4. Open: INVOICE REVERSALS FOR REVIEW WITH TOTALS
    1. Analysis tab: DIRECT EXTRACTION
      1. Create new file: INVOICE REVERSALS
      2. Criteria: (NO_OF_RECS % 2 = 0 .AND. AMOUNT_SUM == 0) .OR. (PREC_NO < PREC_NO_MAX  .AND. ABS_AMOUNT = AMOUNT_SUM)
  5. Open: Open: SORTED INVOICES
    1. Analysis tab: JOIN
      1. Primary File: Open: SORTED INVOICES
      2. Secondary File: INVOICE REVERSALS
      3. Match:
        1. PREC_NO
      4. Match Type: NO SECONDARY MATCH
      5. Create new file: INVOICES WITHOUT REVERSALS

There are 36 reversals (18 pairs) in the sample file, 20 instances (10 pairs) where the amount is reversed and later posted again. The CONTROL TOTALS for the AMOUNT field should match before and after. The point is to remove reversals PRIOR to running any analytics to identify duplicate transactions.


Best Practices , CaseWare IDEA , Tech Tip



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Explore the IDEAScript Vault
Oct 20 The IDEAScript Vault has over 80 IDEAScripts available to any supported user. They are intended as samples and instructional materials, so you should be sure to...
Lead Don't Lag - Pointers from the Pros
Aug 28 Global consulting firm Protiviti recently released its 2019 Internal Audit Capabilities and Needs Survey, which revealed that while most internal audit groups c...
Infusing IDEA into Your Organization
Nov 16 Building a successful and sustainable data analytics program requires a mix of people, processes, and products. At the IDEA Innovations Conference in Houston, l...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×