Join tables Version 1 (Net)
Action group: Tables
Description
The action is designed to merge data from two tables.
Action icon
Settings of parameters
| Property | Description | Type | Filling example | Mandatory field |
| Parameters |
| First table | The table to which the data from the second table will be added. The results of the action will be stored in the current table. | Robin.DataTable |
| Yes |
| Second table | The table whose data will be added to the first table. | Robin.DataTable |
| Yes |
| Join type | The type of table join operation. | Robin.String |
| Yes |
| The columns by which the tables will be merged. | A collection of condition strings for joining tables on the given columns. Where each line is a condition of the form: [column of the first table][operation][column of the second table]. Spaces are allowed between data. Instead of a column name, you can use its index, the numbering starts from 1. If we refer to a column by its name, then we select the name with curly braces: {column name}. List of valid operations: =, <>, >, <, >=, <= Collection string examples: {ID} = {UserID}, 1>{UserID}, 1 <> 5. | Robin.Collection |
| Yes |
Special conditions of use
Tables with different number of columns can be submitted when performing the action, they are joined to the right side of the first table. If the tables to be merged have the same column names, duplicate column names must be assigned a number.
Logic of the "Join type" dropdown list:
- Inner join. Values that match horizontally are joined. The missing columns are added.
- Left join. All values from the first table are taken and only the values that match from the second table are added. Zero values are inserted into the column for rows from DataTable1 that have no match in the rows of DataTable2.
- Full join. All data from all tables is taken, spaces are added where keys do not match. Null values are added to rows from both tables that do not match.
The joined columns must have identical semantics, i.e. both columns must have the same logical value. Merged columns do not necessarily have to have the same name (or even the same data type), although this is often the case.
You can merge the same table, the robot will not generate an error.
All changes are applied to the first table.
Condition | Input data | Result |
|---|
Connection type - Inner {ID} = {ID}
| DT1 
DT2 
| |
Connection type - Left {ID} = {ID} | same | |
Тип соединения - Полное {ID} = {ID} | same | |
Connection type - Full {rank_id}={rank_id} {store_id}={store_id}
| https://learndb.ru/articles/article/28 | |
Description of the parameter Connection condition Robin.Collection:
- A collection of condition rows to join tables based on given columns.
- Each row is a condition of the form: [column of the first table][space][operation][space][column of the second table] OR [column of the first table][operation][column of the second table]. That is, the condition can be entered with or without spaces. It will still be recognized correctly by the robot. The name/index of the column from the first table is always specified on the left; the search for the column with such name or index is performed only in the first table. Similarly, the name/index of the column from the second input table is specified on the right.
- List of allowable operations: =, <>, >, <, >=, <=
- Instead of the column name, you can use its index, numbering starts from 1.
- When writing a water line join condition, you can specify a column of one table by its name and a column of another table by its index.
- Название столбцов должны быть указаны в {}, а индекс столбца - без скобочек, т.е. вот так {5} - это название, просто 5 - индекс.
- If the user specified a column name without {}, the action will not return an error immediately, but will try to find the value among the column names; however, if such a name consists of several words with spaces, the robot will return an error of more operators. A column name enclosed in {} can contain any number of spaces; e.g. {ID} = {ID}, the action will not return an error.
- If an incorrect string is given as input, the robot could not recognize it because it has more operators than it needs, then the robot will generate an error.
- Multiple conditions can be specified in a collection. Each condition must be recorded in a separate element of the collection.
- If several join conditions are specified, the action of joining tables must take into account all specified conditions, not any of them. I.e. there is a logical "AND" between the conditions, not "OR".
- Examples of collection strings:
ID = UserID
{ID} = {UserID}
1 > UserID
1 <> 5
1>UserID.
Example of use
Task
There are 2 tables in Excel, you need to merge them by "inner" type of connection (Horizontally merge values that match. The missing columns are added). Merge them by the following condition: {Job} = {Code}
и 
Solution
Use "Open", "Take table", "Join tables" actions.
Implementation
- Assemble a robot consisting of actions:
- Set the parameters of the "Open" action for the first and the second document (table).
- Set the parameters of the "Take table" action for the first and the second document (table).
- Set the parameters of the "Join tables" action. In the "Connection condition" field specify: {Job} = {CODE}
- Click "Start" in the top panel.
Result
The program robot completed successfully.
The tables are joined. The result of merging the tables will be added to the first input table.
