Filter Table
Verb: filterTable
Available from: <Standard>
Filters the values and structure of a data table according to rows or a desired value.
A table structure is its rows, columns and table styles.
Syntax
filterTable [--indexes(List<Numeric>)] [--where(String)] --dataTable(DataTable) (DataTable)=value (Numeric)=rows (Numeric)=columns
Inputs
| Script | Designer | Required | AcceptedTypes | Description |
|---|---|---|---|---|
| --indexes | Indexes | Optional | List<Number>, List<Text>, Number | Index for data table rows to be filtered.
1. The numbers entered must be between 1 and 2147483647. If it exceeds that range, the message "Value out of range" is returned. 2. When the value of the Indexes parameter does not match any row in the table, the following error message appears: The source contains no DataRows. |
| --where | Where | Optional | Text | Text responsible for finding a given value in a data table cell.
1. The syntax used is based on "LINQ C #" query language standards. To filter by specific column data, for example, you can use the following option: Company = 'IBM Robotic Process Automation'. All rows with this value will be copied. 2. The comparison operators used in this parameter are:>, <,> =, <=, <>, OR, AND, LIKE>%, IN, LIKE *, and NOT. |
| --dataTable | Source | Required | Data Table | Data table that is filtered. |
Outputs
| Script | Designer | AcceptedTypes | Description |
|---|---|---|---|
| value | Table | Data Table | Returns a data table with the filtered columns and rows. |
| rows | Rows | Number | Returns the number of rows that were filtered. |
| columns | Columns | Number | Returns the number of columns that were filtered. |
Example
Example 1: The Filter Table command filters in the data table, assigned to the variable "excelTable", the cells with the value "Sao Paulo" belonging to the column "City", assigning this filtering to a new variable called "newExcelTable" , and return the number of rows and columns filtered.
defVar --name excelFile --type Excel
defVar --name excelTable --type DataTable
defVar --name newExcelTable --type DataTable
defVar --name rowsQuantity --type Numeric
defVar --name columnsQuantity --type Numeric
// Download the following file to execute the command.
excelOpen --file "tableExcelCompanyCity.xlsx" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet --entiretable --hasheaders excelTable=value
//
filterTable --where "City = \'Armonk\'" --dataTable ${excelTable} columnsQuantity=columns rowsQuantity=rows newExcelTable=value
logMessage --message "Table with filtered data: ${newExcelTable}\r\nRows Quantity: ${rowsQuantity}\r\nColumns Quantity: ${columnsQuantity}" --type "Info"
// This example returns the following output:
// Table with filtered data:
// IBM Corporation, Armonk
//
// Rows Quantity: 1
// Columns Quantity: 2
Example 2: Filters in the data table, assigned to the variable "tableExcel", the value on the rows with the indexes "1" and "3", and returns the number of rows and columns filtered.
defVar --name excelFile --type Excel
defVar --name excelTable --type DataTable
defVar --name newExcelTable --type DataTable
defVar --name rowsQuantity --type Numeric
defVar --name columnsQuantity --type Numeric
defVar --name excelTableIndexList --type List --innertype Numeric --value "[1,3]"
// Faça o download do arquivo a seguir para executar o comando.
excelOpen --file "tableExcelCompanyCity.xlsx" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet --entiretable --hasheaders excelTable=value
//
filterTable --indexes ${excelTableIndexList} --dataTable ${excelTable} newExcelTable=value rowsQuantity=rows columnsQuantity=columns
logMessage --message "Table with filtered data: ${newExcelTable}\r\nRows Quantity: ${rowsQuantity}\r\nColumns Quantity: ${columnsQuantity}" --type "Info"
// This example returns the following output:
// Table with filtered data:
// IBM Robotic Process Automation, Toronto
// IBM Robotic Process Automation, Armonk
//
// Rows Quantity: 2
// Columns Quantity: 2