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
Achieving Relevance
Aug 19 Cory Windham, Sarah Palombo – August 18, 2015“Relevance” has become a buzzword among audit professionals as of late. It encapsulates the r...
Analyzing Culture
Nov 26 The average person spends more than 90,000 hours of their lifetime at work. Where we spend that 1/3 of our lives matters. Company culture is important to employ...
Tech Tip: Finding Unique Records
Jul 21 CaseWare IDEA® V11.1 gives you access to a growing library of popular audit-specific apps that make common tasks effortless. By upgrading to the latest version...
BROWSER NOT SUPPORTED

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

×