The 21st Minute


Blog Image

Tech Tip: Duplicate Identification

Raw data is often incomplete, messy and inconsistent. One of the first steps in the data preparation process is the detection and removal of duplicate records. This Tech Tip provides step-by-step instructions for identifying duplicates to improve data quality and integrity.

This example uses an INVOICE FILE, however, these same steps could be applied to other files.

    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. Create new file: INVOICE REVERSALS
        1. Fields to match (example)
          1. VENDOR
          2. ABS_AMOUNT
          3. VENDOR_INVOICE_NO
          4. INVOICE_DATE
          5. PO_NO
        2. Fields that must be different
          1. AMOUNT
    3. Analysis tab: JOIN*
      1. Create new file: INVOICE FILE – NO REVERSALS
        1. Primary File: INVOICE FILE
        2. Secondary File: INVOICE REVERSALS
        3. Match: PREC_NO
    1. Analysis tab: DUPLICATE KEY – DETECTION
      1. Create new file: INVOICE DUPLICATES
        1. Fields to match (example)
          1. VENDOR
          2. AMOUNT
          3. VENDOR_INVOICE_NO
          4. INVOICE_DATE
          5. PO_NO

* NOTE: Systems like SAP may have multiple reversals on a PO, thus removing reversals requires more than one “pass” on these two steps – usually dropping INVOICE_DATE (as a match) on at least one pass.

Try it yourself! This mock data matches the column names used above. There are 14 reversals and 6 duplicates.

Sample Invoice Data File.xlsx

Let’s Go!

Best Practices , CaseWare IDEA , Data Analytics , 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
More Prep = Less Fieldwork
Sep 25 Quick Tips for Using IDEA to Stay Ahead Summer has wrapped, school is in session and fall is here! And just like a boomerang, busy season will be back around...
Gaining an IDEAScript Advantage
Sep 25 CPA Firm's Journey in Becoming Data-Analytics Driven   A group of data analytics enthusiasts at Schneider Downs & Co. asked staff and senior-level ...
Tech Tip: Creating Separate Files for Summarizations
Sep 25 Follow these simple steps to create and export separate files based upon your summarization: Open the original file, in this case SAMPLE-CUSTOMERS Fro...

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