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.”
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
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.