Compare queries have 3 types of columns, Keys, Values and Informational. When two data sources are compared, all value columns use the value comparison to determine if an error should be raised.
Value comparison consists of error and threshold options, each having 3 modes.
The error options decide in which cases comparison of two values results in the compare query raising an error.
The error options are:
- Value in first dataset doesn’t equal value in second dataset
- Value in first dataset is more than value in second dataset
- Value in first dataset is less than value in second dataset
Option 1 is the default option, which simply compares the two values and raises an error if they are not the exact same value. Option 2 and 3 only raises errors if the values are not equal and the larger value is in the correct data source.
Imagine a scenario were we are comparing planned costs versus actual costs. It would be an error if actual costs are higher than planned costs, however, it would be fine if the actual costs are lower than the planned costs.
Threshold is the allowed difference between two values, that will not result in an error.
To set the threshold, user has 3 options:
- No threshold
- Value threshold
- Percentage threshold
The first two options are calculated the same, it’s a simple value, where comparer raises an error if the difference is more than the threshold value.
Percentage threshold is calculated as the difference between the first and second data source divided by the second data source, or: ((Datasource2 value – Datasource1 Value) / Datasource2 Value)
To visualize these differences lets compare two data sources, Planning and Actual against each other with different options. Both will contain the column Cost, with Planning’s column having the value 1000 and Actual having 900.
The results can be summarized as such:
- Not Equal and Planning more than Actual both always create errors unless the threshold is more than the difference between the values.
- Planning less than Actual never creates an error, irrelevant of threshold values.
- When the threshold is more than the difference of values, an error is never created.