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

 

 
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

 

 

• 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

 

 
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