User Tools

Site Tools


transformations:keepremovematching

KEEP/REMOVE MATCHING

Category: Transform / Filters


Description

This action keeps or removes rows where values in the specified column are matching the specified column of another table.


Use cases

Finding the difference between two lists, or the overlapping records of two lists.

Filtering a table to keep only records with a subset of values in a particular column.

Finding new records that haven't been exported yet.


Action settings

Setting Description
ModeSelect whether values in the current dataset are kept or removed if they match values in the lookup table. Options: Keep
matching (remove mismatching)
or Remove matching (keep mismatching).
Lookup tableSelect the dataset containing the values to compare to.
Matching columnsSelect the columns in the current table and the lookup table to match values in. This option appears once the Lookup
table has been selected.


Remarks

This action is case-sensitive and will only match values with the same case.

Values must be identical (equal) in order to be matched. If another matching mode is needed (e.g. "Starts with") use the Match action first to do the matching.

The action's icon will change depending on the mode selected (keep/remove).


Examples

Example: Remove the European countries from the first table.

Table 1: Average income and deductions

Country Gross income Disposable income Compulsory deductions
United States 56067 4558218.7%
Ireland 51218 480736.14%
Luxembourg 55176 4771613.52%
Australia 51050 4261716.52%
Switzerland 53716 484149.87%
Canada 45896 3746918.36%


Table 2: Some European countries

Country
Ireland
Luxembourg
Switzerland


Action parameters:

Mode is "Remove matching"
Matching values in table is "Table 2"
Column of the first table is "Country"
Column of the second table is "Country"


Result table:

Country Gross income Disposable income Compulsory deductions
United States 56067 4558218.7%
Australia 51050 4261716.52%
Canada 45896 3746918.36%


Community examples


See also

transformations/keepremovematching.txt · Last modified: 2021/07/19 02:12 by craigt