Claim Identifiers and Duplicate-Count Controls

Why Identifier Controls Matter

A loss file can contain several rows for one claim, or several legitimate claims under one policy. Counting every row as a claim can overstate frequency; collapsing every row with the same policy number can understate it. The first control is therefore to decide what one row is meant to represent, then use identifiers that distinguish those units.

This tutorial focuses on claim, policy, and coverage identifiers in a claim-level or claim-coverage-level data extract. A repeated identifier is a signal to investigate, not automatic proof that a row should be deleted. In particular, this discussion does not treat every repeated claim identifier as a duplicate transaction or prescribe how claim transactions are totaled.

A policy identifier usually identifies a policy, not a claim. One policy may have multiple claims. A claim number may identify a claim only within a particular policy or administrative system; the same number can be reused elsewhere. A coverage identifier distinguishes coverage types, but whether coverage belongs in the key depends on the intended unit. If the measure is a count of claims, one claim with two coverages is still one claim. If the measure is a count of claim-coverage units, those coverages may be separate units.

A useful key must be complete, stable, and sufficiently specific for the intended population. For example, a claim key might be the combination of policy identifier and claim identifier. If claim identifiers can be reused across systems, a system identifier may also be needed. For claim-coverage units, the key may additionally include coverage. There is no universally correct key independent of the data grain and the identifier rules.

Counting Rows and Distinct Keys

Suppose a file has \(R\) rows with valid keys. Let \(K\) be the number of distinct keys, and let \(n_j\) be the number of rows carrying distinct key \(j\). The rows beyond the first occurrence of each key are a useful reconciliation measure.

The equality holds because each key contributes \(n_j\) rows to the file and one first occurrence. Summing the rows gives \(R=\sum_{j=1}^{K}n_j\); subtracting one for each of the \(K\) distinct keys gives \(\sum_{j=1}^{K}(n_j-1)=R-K\). This count measures repeated occurrences of keys, not necessarily erroneous rows. It is a reconciliation, not a deletion rule.

Selecting a Key Without Collapsing Legitimate Claims

The key must distinguish the units being counted. If the intended unit is a claim, grouping only by policy identifier is too broad: all claims on one policy would be combined. Conversely, grouping only by claim number can be too broad when claim numbers are reused across policies. A composite key can preserve the distinction.

The following example uses a hypothetical claim-level extract. The key values have been checked against the stated identifier scope, and each repeated key is under review as a repeated claim-level representation.

When Coverage Belongs in the Key

The key changes when the grain changes. If the row represents a claim-coverage unit, adding coverage to the key can distinguish two legitimate coverage records for the same claim. If the row represents a claim, adding coverage and counting distinct keys would instead count a multi-coverage claim more than once. The analyst should record the chosen grain and key together so that the count has an interpretable meaning.

Missing or Unreliable Identifiers

A row with a missing key cannot be safely classified as a new claim or as a repeat of another claim. Treating every blank as one shared value falsely groups unrelated records; treating each blank row as a distinct claim assumes information that is absent. Report the number of unresolved rows separately and investigate them using an approved source or data-quality process. Do not silently substitute a field that does not have the required uniqueness.

Also check for representation differences that can create false nonmatches: leading zeros removed from an identifier, spaces, inconsistent capitalization, or inconsistent data types. Standardization can help only when it follows documented rules. For example, stripping leading zeros is unsafe if “007” and “7” can be different identifiers. Preserve the original values, document any normalization, and check whether it changes distinct-key counts.

Common Traps and Practical Controls

A defensible duplicate-count control produces both a count and an explanation of its scope. At minimum, retain the row count, the key definition, the number of rows with valid keys, the distinct-key count, and the count of rows beyond first occurrences. Where rows lack keys, report them separately. If an extract is expected to have one row per claim, repeated keys merit investigation; if it is expected to have one row per claim-coverage unit, the key must reflect that unit.

• Counting rows as claims: Rows are observations in a file; they are not necessarily distinct claims.
• Using policy ID as a claim key: This can collapse several legitimate claims under one policy.
• Using claim ID without checking scope: The same claim number may be used under different policies or systems.
• Adding coverage without considering the question: This may be appropriate for claim-coverage counts but overcounts claims with multiple coverages.
• Deleting every repeated key: A repeated key calls for review; the row grain and source context determine whether it is a true duplicate.
• Ignoring blanks: Missing identifiers make distinct counts incomplete or uncertain and should be disclosed.

These controls make counts reproducible and help prevent duplicate representations from distorting loss and frequency measures, while preserving legitimate multiple claims under one policy. The identifier discipline described here is aligned with the CAS Exam 5 (Basic Ratemaking and Estimated Claim Liabilities) syllabus.