Skip to content

StateDMI / Command / JoinTables

Overview

The JoinTables command joins two tables that have matching column values, resulting in the first table being modified to contain additional columns from the second table. Rows from the first table are always retained. Depending on the join method, additional rows may be added in cases where one of the tables does not match the other. All or a subset of the columns and rows from the second table can be included in the result. The functionality of the command is similar to database SQL join statements using left, right, center, etc. join syntax.

Command Editor

The following dialog is used to edit the command and illustrates the syntax of the command (in this case illustrating how values in a column named LocationID are copied to a new table).
See also the full-size image.

JoinTables

JoinTables Command Editor

Command Syntax

The command syntax is as follows:

JoinTables(Parameter="Value",...)

Command Parameters

Parameter&inbsp;                                  Description Default                
TableID
required		 The identifier for the original table. This table will be modified. Can be specified using ${Property}. None – must be specified.
TableToJoinID
required		 The identifier for the table to join. Can be specified using ${Property}. None – must be specified.
JoinColumns
required		 The names of columns to be compared in the join, using syntax, and can use ${Property} syntax:
Table1ColumnName1:Table2ColumnName1,
Table1ColumnName2:Table2ColumnName2		 Required – must specify at least one column to compare
IncludeColumns Specify the names of columns to copy from the second table into the first table, separated by commas. The columns indicated by JoinColumns will not be added because they should already be in the table. Can be specified using ${Property}. Copy all of the columns from the TableToJoinID table.
ColumnMap Specify new names for the output columns being included, using syntax, and can use ${Property} syntax:
OriginalTable2ColumnName1:NewColumnName1, OriginalTable2ColumnName2:NewColumnName2		 Column names in the result will be the same as in the original TableToJoinID table.
ColumnFilters Filters that limit the number of rows being processed, using the syntax, and can use ${Property} syntax:
Table2Column1:FilterPattern1, Table2Column2:FilterPattern2
Patterns can use * to indicate wildcards for matches. Only string values can be checked (other data types are converted to strings for comparison). Comparisons are case-independent. All patterns must be matched in order to copy the row. In the future a command may be added to perform queries on tables, similar to SQL for databases.		 No filtering.
JoinMethod Indicate how the tables should be joined:
  • JoinIfInBoth – only add column values from the second table that have matching values in columns specified by JoinColumns
  • JoinAlways – behave as if JoinIfInBoth is used and also add rows even if join column values do not match. Rows in the second table that don’t match will result in new rows at the bottom of the table, with null/missing values for columns in the first table.
 JoinIfInBoth
HandleMultipleJoinMatchesHow Indicate how to handle multiple matches resulting from the join:
  • NumberColumns – the first match will result in the requested join columns being added to the first table; subsequent matches will result in new columns with _2, _3, etc. appended to the column names.
  • UseLastMatch – the join will occur once and subsequent matches will result in the new column values being overwritten with the last match. In other words, new rows are not added for multiple matches – the same row is overwritten.
 UseLastMatch

Examples

See the automated tests.

Troubleshooting

See Also