IDEA Tech Tip: How to Identify Employees Using P-Cards While on Sick Leave
Q: I would like to know whether any employees have made purchases on their purchasing card while they were on sick leave. I have two datasets: one that details which employees have claimed sick leave with date ranges, and a second one with purchasing card transactions with name on card, purchase dates and other information.
How do I use IDEA to compare the two files and get a list of employees who had purchase card transactions while on sick leave?
A: I will use these two sample files to show how to perform this analysis. The first file is the PCARD file that contains the Employee No along with the Transaction Date and other information related to the transaction.
The second file contains the vacation information, namely the employee number and the start and end date of their vacation.
To perform this analysis we will first create one file by using the Join function. Use a common key between the two files, like Employee No, to perform the Join function—in this example the employee number is common between the two files.
Using this example, the PCARD file would be the primary file and the vacation file would be the secondary as we want to add on the vacation start and end date to the PCARD file. The match would be the Employee No as it is the common field between the two files and we are only interested in matches.
For the secondary file we only really need the vacation start and end date so we can remove any additional files (so as not to clutter the final file).
The next step is to compare the transaction date with the vacation start and end dates; in order to do this, all the fields must be in a date format. You can verify this by opening the Field Manipulation dialog by double clicking within the database.
In this example the vacation start and end date are in the date format but not the transaction date. To change it, click on the type next to the posted date field and change it to Date, and in the parameter I enter MM/DD/YYYY as the information is in the month/date/year format.
Once all the fields are a date we can now extract all the transactions that took place while the employee was on vacation. Under Analysis, select the Direct Extraction and then click on the calculator icon to open the Equation Editor. We can use the @BetweenDate() function to extract all transactions that were made while the person is on vacation. The @BetweenDate() function needs the transaction date, the start date and the end date.
The resulting file gives us all the transactions that took place while an employee was on vacation. In this example, we see Employee 22197 made one transaction and Employee 28398 made three transactions.
– Brian Element, CPA, CIDA, CISE, CFE – IDEA Certified Instructor and Financial Advisor at Public Works and Government Services Canada
For more IDEA tech tips or to ask your own IDEA questions, visit 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