.svg)
Created by: Kristinn Magnusson
In this article, you will read about query value comparisons. Compare queries have 3 types of columns; Keys, Values and Informational. When comparing two data sources, all value columns utilise value comparison to determine if an error should be raised.
Value comparison consists of error and threshold options, each having 3 modes.
Error Options
Error options give you the ability to decide when the comparison of two values should result in a compare query error. The error options are:
- The value in the first dataset doesn’t equal the value in the second dataset
- The value in the first dataset is more than the value in the second dataset
- The value in the first dataset is less than the value in the 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. Options 2 and 3 only raise errors if the values are not equal and the larger value is in the correct data source.
Imagine a scenario where you are comparing planned costs versus actual costs. You would want to be notified, via email, when actual costs exceed planned costs. However, you may not want to be notified if actual costs are lower and subceed planned costs.
Thresholds
The specified threshold is the difference you allow between two values before an error exception is sent to the recipient.
To set the threshold, you have 3 options:
- No threshold
- Value threshold
- Percentage threshold
No threshold and Value threshold are both calculated in the same way. It’s a simple value, where the comparer raises an error if the difference is more than the threshold value.
The Percentage threshold is calculated as the difference between the first and second data source divided by the second data source, as a percentage.
To visualize these differences let's 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.
