Filter Table

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

filterTable [--indexes(List<Numeric>)] [--where(String)] --dataTable(DataTable) (DataTable)=value (Numeric)=rows (Numeric)=columns

Dependencies

IBM RPA Studio instantiates tables with their values in memory. These tables are stored in a Data table variable. You need to use the commands in IBM RPA Studio that return this variable to handle the data table.

Input parameter

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

The indexes parameter

The index is the row number that you want to filter. When you use the Index to filter rows, you need to consider whether the row exists in the source table and has data in at least one cell.

• To filter a specific row in the table, specify the index as a numeric value, for example: 1, 6, or 11.
• To filter a range of rows, use a list variable List<T> as Number or Text inner type and enter the row numbers that you want to filter separated by comma. For example: [2, 5, 7, 11].

The where parameter

The where parameter allows you to filter rows in a data table based on specific conditions.

When you create a Where expression, reference column values by using the column name. The column name can consist of simple sequences of characters or special characters such as ~, (, ), #, \, /, =, >, <, +, -, *, %, &, |, ^, ' ", [, ], and (blank space). Enclose column names containing special characters within either square brackets or the grave accent (`).

Example:

Column name containing special characters: [Column#] or `Column#`.

For information about filtering column names with reserved words, see Expression Syntax 🡥.

Value types guideline

• String values
  Enclose string values within single quotation marks, for example: Name = 'John'. To include single quotation marks within a string value, escape them using another single quotation character, for example: Name = ''John''.

• Date values
  Enclose date values within single quotation marks, for example: Date = '05/05/99'.

• Numeric values
  Define numeric and decimals values directly, without enclosing them within any characters, for example: Goal = 4 or Price = 70.00.

• Operator values
  You can use Boolean operators and comparison expressions:

Boolean operators:

• AND
• OR
• NOT

Comparison expressions:

• <
• >
• <=
• >=
• <>
• =
• IN
• LIKE

For more information about supported Boolean operators precedence and arithmetic operators, see Operators 🡥.

Also, you can use wildcard characters with the LIKE comparison expression. For more information, see about rules and patterns of wildcards, see wildcard characters 🡥.

Aggregate functions

Use aggregate functions by referencing the table column name. The following functions are supported:

• Count()
• Min()
• Max()

Example: Count(Salary) >= 3

Also, you can use the following supported functions:

• LEN(Column name): Gets the length of a string. For example: LEN(Name) = 4.
• SUBSTRING(Column name, start index, substring length): Gets a substring of a specified length, starting at a specified point in the string. For example: SUBSTRING(Name, 1, 3) = 'Car'.
• TRIM(Column name): Removes all leading and trailing blank characters, such as, \r, \n, \t, (blank space). For example: TRIM(Name) = 'User name'.
• IIF(Column name, true statement, false statement): Gets one of two values depending on the result of a logical expression. For example: IIF(Name = 'John', True, False) = True.

Output parameter

Example

Example 1: The following code example demonstrates how to filter a table by using the IN comparison operator to check whether the table has equivalent data. For this example, consider the following scenario.

Assume that you have an excel file that is named "Sample.xlsx" stored in the My documents folder. This file has a data table with a column named "Date" and four rows, two of them filled with date values.

defVar --name excelInstance --type Excel
defVar --name sampleTable --type DataTable
defVar --name filteredTable --type DataTable
defVar --name rows --type Numeric
defVar --name columns --type Numeric
defVar --name documentsFolderPath --type String
defVar --name sampleSpreadsheet --type String
// Gets the 'My Documents' folder path.
getSpecialFolder --folder "MyDocuments" documentsFolderPath=value
// Adds the 'sample.xlsx' file name to the 'My Documents' folder path and stores it in the 'sampleSpreadsheet' variable.
setVar --name "${sampleSpreadsheet}" --value "${documentsFolderPath}/sample.xlsx"
// Open the 'sample.xlsx' excel file.
excelOpen --file "${sampleSpreadsheet}" excelInstance=value
// Gets the sample table from the opened file. The table is stored in cache, so can be handled.
excelGetTable --file ${excelInstance} --getfirstsheet  --entiretable  --hasheaders  sampleTable=value
// Filters and creates a new table if the 'Date' column contains the dates '17-08-2021' and '17-05-2021'. It creates a new table containing the rows equivalent to these two results.
// The expression uses the IN condition.
filterTable --where "Date IN (\'17-08-2021\', \'17-05-2021\')" --dataTable ${sampleTable} rows=rows columns=columns filteredTable=value
// Logs the table and rows filtered and the number of columns in the table.
logMessage --message "Filtered table: ${filteredTable}\r\nNumber of rows: ${rows}\r\nNumber of columns: ${columns}" --type "Info"

Example 2: The following code example demonstrates how to filter a table by using an expression that contains the AND and OR Boolean operators, the Count() aggregate function, and a precedence clause. For the example, consider the following scenario.

Assume that you have an excel file that is named "Sample.xlsx" stored in the "My documents" folder. In this file, you have a data table and in this table, you have the 'Name', '#Surname', and 'Salary' columns, along with four rows with data. One of these rows is filled with the name 'John' and 'Doe', each one in its respective column, with "John" being the name and "Doe" the surname.

defVar --name excelInstance --type Excel
defVar --name sampleTable --type DataTable
defVar --name filteredTable --type DataTable
defVar --name rows --type Numeric
defVar --name columns --type Numeric
defVar --name documentsFolderPath --type String
defVar --name sampleSpreadsheet --type String
// Gets the 'My Documents' folder path.
getSpecialFolder --folder "MyDocuments" documentsFolderPath=value
// Adds the 'sample.xlsx' file name to the 'My Documents' folder path and stores it in the 'sampleSpreadsheet' variable.
setVar --name "${sampleSpreadsheet}" --value "${documentsFolderPath}/sample.xlsx"
// Open the 'sample.xlsx' excel file.
excelOpen --file "${sampleSpreadsheet}" excelInstance=value
// Gets the sample table from the opened file. The table is stored in cache, so can be handled.
excelGetTable --file ${excelInstance} --getfirstsheet  --entiretable  --hasheaders  sampleTable=value
// Filters and creates a new table if the column 'Name' contains 'John' and the column '#Surname' contains 'Doe.' Or the quantity of rows be equal to or more than 3.
// The expression first considers the conditions inside the parenthesis, and then checks the OR condition.
filterTable --where "(Name = \'John\' AND [#Surname] = \'Doe\') OR Count(Salary) >= 3" --dataTable ${sampleTable} filteredTable=value rows=rows columns=columns
// Logs the table and rows filtered and the number of columns in the table.
logMessage --message "Filtered table: ${filteredTable}\r\nNumber of rows: ${rows}\r\nNumber of columns: ${columns}" --type "Info"

Related links

• Add Column
• Add Row
• Check for Column Existence in Table
• Copy Rows
• Copy Table
• Delete Column
• Delete Rows
• Find Column by Name
• Find Table Cell Occurrences
• Get Cell Contents
• Get Column Name
• Get Column Structure
• Get HTML Tables
• JSON to Table
• Map Table Row
• Move Table Rows
• Sort Table
• Update Row
• Write Table to File