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 #1

Keep the rivers in Table 1 that match the Continents listed in Table 2.

Before (source table)

Table 1: The longest rivers in the world

River Length (km) Continent
Nile 6650 Africa
Amazon 6400 South America
Yangtze 6300 Asia
Mississippi 6275 North America
Yenisei 5539 Asia

Table 2: Continent list

Continent name
Africa
South America

After (result table)

River Length (km) Continent
Nile 6650Africa
Amazon 6400South America

Action parameters

Mode: Keep matching
Matching values in table: Table 2
Column of the first table: Country
Column of the second table: Country name


Example #2

Remove the European countries from the first table.

Before (source 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 name
Ireland
Luxembourg
Switzerland

After (result table)

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

Action parameters

Mode: Remove matching
Matching values in table: Table 2
Column of the first table: Country
Column of the second table: Country name


Community examples


See also

transformations/keepremovematching.txt · Last modified: 2025/01/29 09:31 by yurii

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki