Purpose
This query identifies and retrieves records from two tables where the values in the source and target columns differ significantly, both in terms of absolute and relative differences.
Pattern
The pattern joins the tables on matching keys and filters the results to include only those pairs of records where the absolute difference between the source and target values exceeds a certain threshold, or where the relative difference between them is larger than a specified limit.
Parameters
- SRCTBL: This table contains the source dataset.
- SRCKEY: This unique key identifies the object in question.
- SRCVAL: The value to be compared.
- TARTBL: This table contains the target dataset.
- TARKEY: This unique key identifies the object in question.
- TARVAL: The value to be compared.
- ABSDIFF: Sets the threshold for the absolute difference between two values
- RELDIFF: Sets the threshold for the relative difference between two values
Usage
In this case we have a table with the CO₂ emissions per head in tons from 2021 and one table from 2022. We apply the VAL pattern with absdiff = 1 and reldiff = 10% to get all countries where the value increased or decreased for more then 1 ton and more then 10%.
Dataset
CO2 EMISSIONS PER HEAD IN TONS 2021
CO2 EMISSIONS PER HEAD IN TONS 2022
Parameter
- SRCTBL = [DQ-VAL.CO₂21$]
- SRCKEY = Country
- SRCVAL = CO2perHead
- TARTBL = [DQ-VAL.CO₂22$]
- TARKEY = Country
- TARVAL = CO2perHead
- ABSDIFF = 1
- RELDIFF = 0.1
Statement
Output
