Snapshot Datasets: Comparing "Last Two Instances" / Track Changes (Version 5.0.5)
The following article describes how to track changes in paired datasets instances.
This article answers the following questions:
- How to enable Snapshot Dataset mode
- How to find rows that have been added / removed or changed since the previous data collection
- How to apply filters to Changed, New and / or Removed rows
- How to use Derived Fields when comparing instances
1. Comparing Instances is only available for Snapshot Datasets
If in the Dataset Editor > Data tab the Snapshot dataset? field is set to 'yes', than in the Dataset Viewer the following options are available:
- Reviewing a Single Instance of this Dataset; e.g., yesterday
- Tracking changes in the Last Two Instances of this Dataset; e.g., yesterday's data compared to what occurred the day before yesterday).
NOTE: You can compare ANY instance saved during previous data collections to its instance collected the day before.
Choose the date which you want to compare to the prior one
Example: In this example, the instance collected on Friday 04/14/2017 is used, meaning that it will automatically be compared to the instance collected prior to it on Thursday 04/13/2017.
2. Tracking Changed, New and Removed rows
The Track Changes function allows fetching a selection of New and/or Removed and/or Changed rows. Whenever you choose to include more than one Type of rows' alteration; e.g., Changed+New, Changed+New+Removed,, then each row in the Results set is assigned a corresponding label in the Change Type column.
To compare values and track changes / updates in Last Two instances:
- Switch to the Last Two Instances mode
- The Track Changes option is now available for use
- Define whether you want to see which rows have been changed / new / removed since the previous date by selecting the appropriate check boxes
- For each field: specify the field (parameter) by which the comparison is going to be performed
- Click Apply Changes to update the Results set
Results: Only those rows where the values defined during Step 4 have been changed / new / removed since collecting the prior instance are shown.
3. Applying filters to Changed, New and/or Removed rows
NOTE: For detailed information on filters used for comparing instances, refer to: Applying Filters when comparing "Last two Instances"
You can apply different set of filters to the rows that are Changed and to those which are New or Removed.
- In the example above we defined the following filters:
- For Changed rows: only rows where Country = 'Canada'
- For New/Removed rows: where Name = '101-Bottle Wrought Iron Wine Jail'
- Apply Changes to update the Results set
- In the Results set, only requested rows have been fetched.
4. Using Derived Fields when Comparing Instances
Derived Fileds include values that do not exist in a Data Source itself but are calculated from one or more existing numeric fields via basic arithmetic expressions and non-aggregate numeric functions.
NOTE: For detailed information on derived fields, refer to: Understanding Derived Fields
4.1. [Option 1] Constructing Complex Formulas
When constructing a formula for a Derived field in the Last Two Instances mode, you can include the same field, but from current and prior instances.
Derived fields with formulas including current and prior values become UNAVAILABLE when switching to a Single Instance mode.
4.2. [Option 2] Using Derived fields when constructing Filters
Derived fields can also be used to construct filters. In this case they are treated the same way as regular fields if the formula contains only current values (The drop-down lists in filters section will include current and prior value options). See the image below.
Alternatively, if the complex formula of a Derived Field includes both current and prior values which are required for calculation (as shown above), the drop-down lists in filters section will include only current value option.
What would you like to do next?
- Get more information on filters available in the "Last Two Instances" mode
- Learn about Derived fields in Datasets
Other helpful articles: