Join tables Version 1 (Net)

Action group: Tables

Description

The action is designed to merge data from two tables. 

Action icon

Settings of parameters

PropertyDescriptionTypeFilling exampleMandatory field
Parameters
First tableThe 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 tableThe table whose data will be added to the first table.Robin.DataTable
Yes
Join typeThe 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

  1. Assemble a robot consisting of actions:
  2. Set the parameters of the "Open" action for the first and the second document (table).


  3. Set the parameters of the "Take table" action for the first and the second document (table).
  4. Set the parameters of the "Join tables" action. In the "Connection condition" field specify: {Job} = {CODE}


  5. 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.

 

  • Нет меток