Except Component

Except Component



Except Component

The Except component lets users compare two input flows (a primary table and a comparison table) and then output any rows from the primary table that do not also exist in the comparison table.

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


Properties

The table below cites the Except component's setup properties, including any actions required of the user.

Note: care should be taken when casting float types into string types, since this may introduce numerical inaccuracies.

Property Setting Description
Name String Input the descriptive name for the component.
Cast Types Select Choose whether to cast types.
Yes: if the same-named column from both inputs has differing input types, attempt to cast them to a common type. This is not guaranteed to work—please check your data carefully.
No: if the same-named column from both inputs has differing input types, report an error and do not continue.
Primary Table Select Select the input source to treat as the primary table. The other input source becomes the secondary (comparison) table.

Strategy

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


Example

This example includes two tables of user data. One table is an updated version of the other. When viewing the table with updated data, it is apparent that some users have changed their details since the details were last recorded. To build compelling data, we wish to find any data relating to users who have recently changed their details. To do this, we will use the Except component.

The below image shows the data and its updated counterpart.

Several users have changed their "plan". Were this a table with millions of users, it would be beneficial to isolate only the updated records and analyse them. However, if we do not mind which field has changed, and only focus on rows being different now compared to before, we can use the Except component to find all rows that have changed.

For the component setup, in this example we select "Yes" on the Cast Types property, because we want to try to reconcile the data rather than skip the record.

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