Arrive at a Count of Records

It is a common practise to arrive at the Count of Records in IDEA. The Count may serve as ~

 

(a) Data Integrity Check - Completeness Check to Count the Number of Unique Dates in a Month to get say a 30 or 31

 

(b) Duplicate Check - Duplicate Check to look for the incidence of more than 1 record per Unique Field like say Collection Receipt Number

 

(c) Gap / Missing Record Check and more - Gap Check to look for missing Dates in a week for Revenue Assurance

 

The easiest way to prepare a Count of Records in IDEA is to apply Summarization.

 

While the first thought which comes to our mind when we apply the Summarization is that there must be a Numeric Field to Total in the underlying database, the Summarization by default presents the Count of Records even without selecting any Numeric Field to Total.

 

While framing an Analytic Scenario, we need to take care to ensure that do we need to seek the 'Count of Records - Line Items in the File for a Unique Grouping Field' or 'Count of Document Numbers in the File for a Unique Grouping Field'

 

Both the scenarios are quite different. Let us examine both the scenarios with an example.

 

In an Employee Travel Claim data file you have the fields - Claim Number, Claim Date, Department, Employee, Type of Claim, Narration and Amount

 

Now the Claim Number linked to one Employee may have multiple 'Type of Claims' like 'Local Conveyance', 'Food', 'Per Diem', 'Hotel Stay' and other such categories. Each of these categories will appear on multiple lines  (one line per category) in the IDEA file with the same 'Claim Number'.

 

So if we need a report of 'Employee wise Count of Unique Claims' so as to then arrive at the 'Top 10 Employees by say Count of Claims', a simple Summarization on 'Employee' will give 'Count of Records - Line Items per Employee'. This is incorrect. To arrive at the 'Count of Unique Claims per Employee' you would need the individual category of claims viz 'Local Conveyance', 'Food', 'Per Diem', 'Hotel Stay to be grouped up to produce a single line per 'Claim Number'.

 

So the required scenario can be arrived at by Summarizing on the "Employee' and then the 'Claim Number'. This will compress the multiple category line items into a single line per 'Claim Number'. Now on the child file perform another Summarization on 'Employee' only to get the 'Count of Unique Claims per Employee'.

 

Best Regards

 

Group Admin Team