¶ Basic Information
| Key | Value |
|---|---|
| Table | DS12 Variance CAL |
| Severity | CRITICAL |
| Unique ID | 1120502 |
| Summary | Is this VAR CAL entry duplicated by CAL ID & transaction ID? |
| Error message | Count of CAL_ID & transaction_ID combo > 1. |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "Duplicate VAR CAL Entry" is designed to identify any duplicate entries in the DS12 Variance CAL table. This check specifically looks for any instances where the combination of CAL_ID and transaction_ID is repeated more than once in the table.
If the DIQ check returns a positive result, it indicates that there are duplicate entries in the DS12 Variance CAL table. This is likely due to an error in data entry or data import, where the same variance calculation (VAR CAL) has been recorded multiple times with the same CAL_ID and transaction_ID.
The fields causing this issue are the CAL_ID and transaction_ID fields. The expected values for these fields are unique combinations of CAL_ID and transaction_ID for each entry in the DS12 Variance CAL table. If the same combination appears more than once, it is considered a duplicate and flagged by this DIQ check.
To resolve this issue, review the entries in the DS12 Variance CAL table and remove or correct any duplicate entries. Ensure that each VAR CAL entry has a unique combination of CAL_ID and transaction_ID.
¶ Why do we check this?
This test, titled 'Duplicate VAR CAL Entry', is being performed on the 'DS12 Variance CAL' table to check for any duplicate entries in the data. The test is specifically looking for instances where the combination of 'CAL_ID' and 'transaction_ID' appears more than once in the data.
The importance of this check lies in maintaining the uniqueness of each entry in the data. Duplicate entries can lead to incorrect calculations, skewed results, and misleading insights during data analysis. It can also cause issues in data management and processing, as it can be unclear which of the duplicate entries should be used or retained.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if any duplicate entries are found, they must be addressed and resolved before the data can be reviewed. Ignoring this error could lead to significant problems in the data analysis and interpretation stages of the project.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS12_VAR_CAL_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT CAL_ID, ISNULL(transaction_ID,'') transaction_ID
FROM DS12_variance_CAL
WHERE upload_ID = @upload_ID
GROUP BY CAL_ID, ISNULL(transaction_ID,'')
HAVING COUNT(*) > 1
)
SELECT
C.*
FROM
DS12_variance_CAL C INNER JOIN Dupes D ON C.CAL_ID = D.CAL_ID
AND ISNULL(C.transaction_ID,'') = D.transaction_ID
WHERE
upload_ID = @upload_ID
)