Subtract

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Subtract

Description

Remove rows from the top dataset with key values that are present in the lower dataset.

 

Example

Subtract from the top dataset all the rows with IDs in the bottom dataset:

 

how to subtract rows from a dataset with ids in another dataset example

 

Inputs

Two.

 

Options

The output depends on the vertical (Y-axis) position of the inputs.

Click Explore Keys... to compare key values in the 2 datasets.

Select Top key column for the column you want to match in the top input dataset.

Select Bottom key column for the column you want to match in the bottom input dataset.

Set Remove according to which row matches you wish to remove:

oAll matches to remove every row from the top dataset where the key exists in the bottom dataset.

oFirst match to remove 1 row in the top dataset for each matching key in the bottom dataset, proceeding from first to last row in the top dataset.

oLast match to remove 1 row in the top dataset for each matching key in the bottom dataset, proceeding from last to first row in the top dataset.

 

Remove options

 

Check case sensitive to use case sensitive matching for keys.

 

Notes

The Info tab shows some examples of rows removed.

This transform is sometimes called an anti-join.

If there are 10k rows or less in both datasets, Easy Data Transform will try to guess sensible default values for Top key column and Bottom key column based on column header names and contents.

If the first input has a header, this will be used for the output.

All values are treated as text and comparisons are whitespace sensitive. You can use Whitespace to remove whitespace before the subtract.

All rows in the top dataset with a given key value are removed if that key value occurs 1 or more times in the bottom dataset.

You can use Concat Cols to join several columns together (e.g. 'first name' and 'last name' columns) to form a key column.

You can use Row Num to create a unique key column.

Use a second Subtract transform to see what was subtracted:

 

How to see what has been subtracted

 

See also

Intersect