IDEA Users Working Remotely: We’re here to help! Please contact our IDEA Help Desk to ensure there is minimal disruption to your daily workflow.

X
Icon


Blog Image

Tech Tip: Range Join


Our IDEA Help Desk frequently receives calls where they need to combine sales transactions and with time-sensitive contract pricing and verify clients were charged the accurate price for date of purchase.

 

IDEA 11.1 significantly reduces the steps needed to distill your data and find matches.

 

Here’s how you do it:

  • Download and install IDEA Labs, RANGE JOIN.
    The RANGE JOIN plugin joins records between two databases based on a range.
  • Matching/joining on range only:
    Select primary database, primary field for range. Select secondary database, secondary field rage 1 (start) and secondary field range 2 (end). Click “OK.”
  • Matching/joining on range and secondary field:
    Select primary database, primary field for range and optional primary field. Select secondary database, secondary field rage 1 (start) and secondary field range 2 (end), and optional secondary field. Click “OK.”
  • Resulting databases:
    • Append Databases – Work file, click “OK” to delete
    • Final Join Only Matches – Contains the sales transactions joined to the correct effective/end date range
    • Final Join No Matches – Contains “orphans” from the primary database where no match was found

 

Range Join Configuration Test
 
To perform the same task using IDEA 9 or 10, follow these steps:

  • Open the sales transaction database and select “Visual Connector” task from “Analysis” tab
    • Add the contract pricing table
    • Add join on key for an exact match, i.e., product, or in this case, customer/product key

 

Visual Connector: Save File As

 

• The resulting database will contain one record for each key and date possibility
• Perform “Direct Extraction” criteria @BetweenDate(SALES_DATE, EFFECTIVE_DATE , END_DATE ) to get final results

 

Direct Extraction
 
To upgrade to IDEA 11.1, please contact [email protected]

If you need help using a specific IDEA feature or function, or even have general technical questions, our Help Desk is ready to support you every step of the way!


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
Conflicts of Interest
Nov 15 Conflicts of interest (COI) occur when an employee must choose between the financial interests of the company and their own personal benefit. Over the years, I...
To Catch A Fraudster
Nov 12 In support of Fraud Awareness Week, Audimation would like to share the most common frauds, and discuss how the fraudsters are changing the way we audit. Acc...
Task Automation Using IDEA
Apr 16 Audit scenarios rarely require an entirely unique process. Having a preferred set of tests ready to go is a great time-saver, but that can be further improved i...
BROWSER NOT SUPPORTED

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

×