<< Click to Display Table of Contents >>
Navigation: Reference > Transforms >
Removes rows based on number, date and text values in selected columns.
Remove rows where all values are empty.
Keep only rows where the 'Tax' value is greater than 0 and the 'Country' value is 'US'.
Remove rows where the 'Transaction' value is greater than the 'Threshold' value (using a column variable).
•Click the button to add a new filter criteria.
•Click the button to delete the selected filter criteria.
•Click the button to copy terms to the clipboard.
•Click the button to paste terms from the clipboard.
•Click the button to clear all terms.
•Select Keep if you want to keep matching rows and Remove to remove matching rows.
•Select Matching all to match on all criteria (e.g. criteria 1 and criteria 2). Select Matching any to require a match on one or more criteria (e.g. criteria 1 or criteria 2).
•Set Column to the column you wish to match.
•Set Op. to the comparison operator.
•Set Value to the value you wish to compare. You can use a column variable.
•Check case sensitive to use case sensitive matching for text.
•Check disable filtering to turn off filtering. If sampling is disabled, the transform does nothing.
•A text summary of the filters is shown:
•A filter row is ignored if the Value column is empty , except when Op. is Equal to, Not equal to, Matches regex or Doesn't match regex.
•Number, date and text values are treated differently for Equal to, Greater than, Less than, Greater than equal, Less than equal and Not equal to operations.
oIf both values are numeric, a numeric comparison will be carried out.
oIf both values match a supported date formats in Preferences, a date comparison will be carried out.
oOtherwise the values will be treated as text.
oFor example, an empty value is considered less than 0, because they will be compared as text. So you might want to replace empty values with 0 or remove those rows with Filter, before comparing them.
•All values are treated as text for Contains, Starts with, Ends with, Matches regex, Is not, Doesn't start with, Doesn't end with and Doesn't match regex operations.
•Comparisons of text are whitespace sensitive. Cells with whitespace will not match Is empty. You can use Whitespace to remove whitespace before filtering and Replace to get of other unwanted characters (e.g. whitespace inside the text).
•See here for more details on Regular expressions (regex).
•Y can use Subtract to see rows removed by filtering if there is unique key column.
•If you are pasting in filter terms, the format expected is comma delimited text with 3 columns.
oThe first column is the 0-based index of the Column drop-down list.
oThe second column is the 0-based index of the Op. drop-down list.
oThe third column is the Value.
For example, pasting in:
You can also try copying terms to the clipboard and pasting into a text file to see the format. If you only paste in 1 column of text, it will be assumed to be a list of Value. You can, of course, use Easy Data Transform to create the appropriate filter terms and paste them into memory.