Detect Changes Component
Detect Changes Component
Scan two separate yet similar tables, and insert a new column detailing if data has been inserted, deleted, changed or is unchanged.
Any rows with key columns containing null values are ignored. Null comparison values are considered equal.
|Name||Text||The descriptive name for the component.|
|Master Table||Selection||Select a 'master' table from the two inputs. This table is the one treated as default in the comparison.|
|Match Keys||Select Many||The keys to join the two tables on. These must appear in both tables.|
|Compare Columns||Select Many||The columns to check for changes. Just like the keys, these must appear in both tables, however the two lists should not overlap.|
|Output Column Mapping||Grid||A mapping of the remaining columns to output names. Sensible defaults are provided, however these can be changed.|
|Indicator Column||Text||This is a new column name in the output. It contains an indicator, showing if the record is "I" (Identical), "N" (New), "D" (Deleted) or "C" (Changed). Switching the Master Table parameter will reverse the meaning of New and Deleted.|
Detects changed, added or deleted data in a comparison table relative to the designated 'Master' table.
IndicatorsIndicators are single-letter codes that indicate what the state of a row is with regard to Detect Changes. The table below shows all indicators and their meanings.
|I||Identical: The same record is present in both tables with no changes.|
|N||New: The record is not present in the master table but is present in the second table.|
|D||Deleted: The record is present in the master table but not in the second table.|
|C||Changed:The record is present in both tables with different values but with the same ID.|
In this example, we have two tables containing similar data and we want to quickly find which rows are different between the two. Our main table contains data for tasks that are to be completed and the second table holds similar but updated data on those tasks. The Transformation job is shown below.
The two tables feed into a single Detect Changes component. We have chosen the original table of task data to be the 'Master' Table, although it matters little in this case since both lists are complete and very similar. We choose to match the two tables by their ID, which is the ID of the task and as such does not change. We then choose to compare the tables via the 'status_id' column, containing values that change according to how near to completion a given task is.
It is instructive next to review the data we are feeding into the Detect Changes component. Below we see the two sets of data. Note they are very close to identical with exception to the 'status_id' values which can change over time.
Running this job will produce the data sampled below. Each row is identified by the ID number and has an associated 'indicator' column. This indicator tells us how each particular row has changed. In this case we can see that many rows are identical (I). However, some rows have been changed (C) and so we know those tasks have been worked on.