Range Checks in IDEA - Case Study on Overtime

 

Range checks in IDEA form an important part of macro level analytical reviews. Such checks let the user gather immediately transactions which are outside the standard limit and need to be investigated.

 

Let us explore Range checks with a case study.

 

You are provided with two files. One - the Attendance File which contains Employee wise, Date wise, Normal Hours worked with Shift details.

 

Second (Other) - the Overtime File which contains Employee wise, Date wise, Overtime Hours, Shift wise.

 

Your requirement is to collate the two files together to identify specific Employees and Dates where the Total Hours (i.e. - Normal Hours plus Overtime Hours) has exceeded 24 hours. This is the range check in IDEA as 24 hours is the upper threshold for maximum hours in any given Date.

 

The steps to execute this case in IDEA is ~

 

(a) Ensure the Field Headings in both the Files viz Employee Code, Employee Name, Date, Hours Worked are the same

 

(b) Use Analysis - Relate - Append Databases to merge (concatenate) the two files together into a single database. This will combine the Normal Hours worked file and Overtime Hours worked file into a single file.

 

(c) Perform Analysis - Categorize- Summarization (on b above) on Fields to Summarize being Employee Code and Date. Numeric Fields to Total being Hours Worked. This will help us arrive at the Total Hours Worked per Employee per Date

 

(d) Apply Analysis - Extract - Direct Extraction (on c above) with the criteria Sum of Hours Worked > 24

 

The final step in d above will help us extract all Employee Date combinations where the range check of 24 hours has failed.

 

Just for illustration, another area where Range Checks can be applied in IDEA is to identify out of Regulatory range - Count of Money Transfers between two Countries to identify possible terror financing or narcotics financing.

 

Best Regards

 

Group Admin Team