IDEA Tech Tip: How to Find Transactions That Only Have One Value
Q: I have a file with sales transaction information and I want to find out how many customers we only had one sale to. What’s the best way to do this in IDEA?
A: You can do this several different ways in IDEA. The first is to perform a summary, an extraction and finally a join. If we use your example of a Sales Transaction file, we can first do a summary by Customer Number, and then do an extract of all the Customers that have one transaction. After that we can join the extraction file back to the Sales Transaction file to extract all the transactions.
First I select Summarization from the Analysis ribbon. IDEA will show the following dialog in order to perform the summarization:
I then perform the summarization on the CUSTOMER_ID field. Selecting a numeric field is optional as IDEA will generate the number or records field by default.
As you can see, there are several ones in the Number of Records field:
I would then extract all of these from the number of records using the Direct Extraction option under the Analysis ribbon.
The resulting file includes all the customers that only had one transaction.
The final step is to join the original Sales Transaction file to this file to extract the transaction.
The primary file would be the Sales Transaction file because we want to match the sales transaction with the file that contains the single transaction customer. The secondary file is the single transaction customer file that I extracted from the summary file. I then select matches only because I just want the matches and the Match Key Fields would be the Customer ID.
Finally I obtain a file that contains all Customers that have only one transaction in the Sales File.
Now let’s look at another way to do this. Under the Analysis ribbon, select Duplicate Key and Detection.
The detection has an option to “Output records without duplicates”, in this case customers that only have one sale.
I have to select the Key, which would be the Customer Number.
Then I select OK to define the Key and OK to run the test. I’ve now obtained the same results in just one step.
– Brian Element, IDEA certified instructor and Financial Advisor at Public Services and Procurement Canada
For more tips or to ask your own IDEA question, visit www.ideascripting.com.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.
Connect: Alain Soublière