Except Component

Except Component

Compares two input flows and outputs any rows from the primary table that do not exist in the comparison table.

Because the entire row is compared, only overlapping columns are considered, otherwise all rows would always look unique and be passed on. Only unique rows are passed to the next component if there are duplicate rows in the primary input source.

Properties

Property Setting Description
Name Text The descriptive name for the component.
Cast Types Select Yes - If the same-named column from both inputs have differing input types, attempt to cast them to a common type. This is not guaranteed to work, so you should check your data carefully.
No - If the same-named column from both inputs have differing input types, report an error and do not continue.

Note: Care should be taken when casting float types into string types as this may introduce numerical inaccuracies.
Primary Table Select Select the input source to treat as the 'main table'. The other source becomes the secondary table.

Strategy

Generates multiple select queries separated by EXCEPT.
Generates multiple select queries separated by aLEFT JOIN.

Example

In this example we have 2 tables of user data - in fact, one is an updated version of the other. When viewing the updated data, it is apparent that some users have changed their details since they were last recorded. In order to build compelling data, we wish to find the data of users who have recently changed their details. To do this, we will use the Except component. Below we show the data and its updated counterpart.

As can be seen, several users have changed their plan. Should this be a table with millions of users, it would be beneficial to isolate only the updated records and analyse them. If we do not mind which field has changed and only care that a row is not as it was before, we can use the Except component to find all rows that have changed.

There is little to do with the component properties. We choose to cast types because it is preferred to try and reconcile the data than to skip the record... but it is unlikely to be important since we know these data are of common types. We also select a primary table, which in this case matters little. The primary table can often be an important consideration, however, as any data in the primary table that is missing from the comparison table, will also be output by the Except component.

Finally, we can take a sample of the data output from Except and find the unique rows.