Audimation Services has been acquired by Caseware International Learn More.


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
Tech Tip: Understanding Join and Visual Connector
Jun 19 Using IDEA’s Join and Visual Connector features can help you search for matches and correlations between different data sets, but they are often confused with...
Tech Tip: SmartAnalyzer, Tagging Fields
Feb 26   A majority of the SmartAnalyzer apps have tags. If you simply select TAGGING without first selecting an individual test – all tags are displayed. St...
Tech Tip: Other Ways to Get IDEAScripts
Sep 18 The ability to automate a process can be very helpful in making your job easier. In IDEA, this means creating macros or IDEAScripts that automatically perform m...

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