Versions:4.5+
Comparisons with Thresholds
By default, QueryPairs execute with an "exact-match" comparison - if the data differs between any pair of Source and Target "cells" in any way, the QueryPair fails. In certain cases, you may want to allow for a range of acceptable values when testing your data. QuerySurge lets you set a threshold for any numeric data-type columns. When the QueryPair results are evaluated, the Threshold values will be applied to the comparison for each of the specified columns.
Note: We strongly recommend that you order your Source and Target queries (i.e. using an ORDER BY clause on both queries so that they are identically ordered) so that your results are more readable, and that you use Shared Keys wherever possible (or comply with our "implicit keys" assumption). See our Key Column Specification article for more information.
Column Threshold Prerequisites
Because the idea of threshold-based comparisons applies only to numeric types, you must have Data Type Checking turned On (otherwise all columns are treated as string data). This is the default setting for QueryPairs, so you'll have to turn data type checking on only if it was previously turned off.
Note: The Data Type Checking On radiobutton must be selected in order for Thresholding to be applied.
Column Threshold Settings
Threshold values can be set under the Properties tab for each QueryPair; the Thresholding feature offers either percentage based or absolute value based threshold evaluations.
Column Origin
The Column Origin can be from either the Source or Target query of your QueryPair. Any delta in the values is measured against the selected Origin Column during comparison.
Percentage-Based and Absolute Thresholds
Consider a Source column S and a Target column T. If we set up a percentage based threshold based on S (i.e. we select From Source Query), for percentage P, then the QueryPair will pass if, for each row in T:
T ≥ S - (P% · S) and T ≤ S + (P% · S)
In other words, each value in T must be in the range defined by S ± P%.
Likewise, for an absolute threshold of A if the Target is within ±A of the Source, the QueryPair will pass, which means that T is in the range defined by S ± A:
T ≥ S - (A) and T ≤ S + (A)
If we had selected From Target Query in our setup, the roles of the Source column S and the Target column T are reversed.
Note: The ranges, whether percentage-based or absolute, are inclusive ranges.
Percentage-Based Thresholds
This option allows the user to define an acceptable percentage deviation from the Origin Column value. Percentage increases and decreases are treated the same way. Acceptable Threshold percent values range from 1E+30% to 1E-6% (0.000001%).
Absolute Thresholds
This option allows the user to define an absolute ± range of acceptable deviation from the selected Origin Column value. Acceptable Threshold values range from 1E+30 to to 1E-6 (0.000001).
Generate Column Names from the QueryPair
The 
button auto-populates all available column names based on your query. You can then select the columns to apply a threshold to:
Add Column Names Manually
There are some cases in which QuerySurge may not be able to parse your query to auto-populate the column names. You can add in column names manually by clicking on the 
button and typing the column name. Column names are returned with your resultset, and it may be helpful to add aliases to your query:
Deleting Column Names
Column names can be deleted individually by clicking on the 
button in each row.
You also can clear all column names by clicking on the 
button.
A Worked Threshold Example
In this example we are looking at QueryPair that compares the Price column for the Product Widget.
When QueryPairs are created the default setting is to have Column Thresholds Options set to Disabled. In this case, an exact match comparison will be performed, resulting in failure.
300 != 100
Percentage-Based Thresholds
If we enable Thresholding for the Price column, we need to specify the range of the threshold. Here we set the Column Origin as the Source Query and the Threshold value to ±150%
Now we see that the Design-Time Runs status is Passed. This is because 100 falls within the ±150% deviation from 300, or between -150 and +750:
300 - (1.5 · 300) = -150
300 + (1.5 · 300) = +750
However, if we switch our Column Origin to Target while leaving the Threshold Value at 150%, we get see that we get a different result.
A failure is now generated when using the Target Query as the Column Origin. This is because 300 is greater than a 150% deviation from 100. Its a 200% change in value, i.e. 300 is outside the range -50 to +250:
100 - (1.5 · 100) = -50
100 + (1.5 · 100) = +250
Absolute Thresholds
If we switch our Thresholds to use Absolute and set our Threshold Value to 200 we get passing results for both From Source Query and From Target Query Column Origin settings. Here 100 is within 200 of 300, i.e. 100 falls in the range of +100 to +500:
300 - (200) = +100
300 + (200) = +500
and 300 is also within 200 of 100 (300 falls in the range of -100 and +300):
100 - (200) = -100
100 + (200) = +300
Important Notes
- Threshold values are inclusive, as noted. This means any differences up to an including the Threshold Value will result in a Pass outcome. Anything outside of the defined Threshold value will result in a Fail outcome.
- Data Type Checking must be enabled for Thresholding to work. QuerySurge will warn you if you attempt to turn Data Type Checking off.
- Threshold values are included in all QueryPair Exports.
- Columns must have a numeric data type defined or be cast as a numeric data-type in your QueryPair. If a column is not returned as a numeric type, the Thresholding checks will not apply.