TableJoin
Requires Analytica 5.1 or later.
This function is currently experimental and not exposed. It could be subject to change in a future release.
Assists in efficient computation of the join of two relational tables. it can perform the Inner (default), Outer, Left, and Right joins. It applies to two relational tables, «x1», indexed by «row1» and «col1» and «x2» indexed by «row2» and «col2». Optional parameter «sharedCol» is a column (or list of columns) which have the same name in «col1» and «col2» over which it performs the join. (If you omit «sharedCol», it joins over all columns with the same name in «col1» and «col2».)
The result is an array with local indexes Pair, with values [1, 2], corresponding to the tables «x1»and «x2», and Row, which ranges from 1 to the number of rows in the joined table. Each row in the result, call it r, contains two values that refer to corresponding rows in tables «x1» and «x2». I.e. r[.Pair=1] contains values from «row1», and r[.Pair=2] contains values from «row2». If you specify «position» to true, it returns the positions in «row1» and «row2» instead of their labels.
To complete the join, you use the result of TableJoin to create a new relational table from the two tables. For example, suppose X1 and X2 are relational tables with local indexes, .Col and .Row.
Index JoinCols := #SetUnion(\col1, \ col2){ Columns for the result }Variable Jresult := TableJoin(X1, X1.row, X1.col, X2, X2.row, X2.col, jointype: 'Inner', position: True)X1[@.row = result[.pair=1], @.col = @JoinCols,defVal: X2[@.row = result[.pair=1], @.col = @JoinCols]]
Parameters
- «x1»: The first relational table, indexed by «row1» and «col1».
- «x2»: The second relation table, indexed by «row2» and «col2».
- «sharedCol»: (Optional) A list of the columns appearing in both «col1» and «col2» that are to be matched during the join. A shared column must have the same label in «col1» and «col2», and it is the label or labels from «col1» and «col2» that are passed in this parameter. When this parameter is not specified, all columns that have the same labels in the «col1» and «col2» indexes, ignoring the final column in each, is used. If you specify
[]for this parameter, a cross-join is performed. - «joinType»: (Optional) Can be
'Inner','Outer','Left'or'Right'. Specifies the join type. - «position»: (Optional) When true, the result contains the positions in «row1» and «row2». When false, contains the labels from «row1» and «row2».
Examples
TBD
Related ways of doing joins
In general, there are alternative ways in Analytica for computing joins when a single column is shared. When multiple columns are shared, then TableJoin is really the only easy way to do it.
Left joins with a single key (column) occur frequently in practice, but are usually done the Slice or Subscript operator. For example, suppose Zip_of_person is indexed by Person and contains the zip code of each person, and County_by_zip is indexed by Zip. Then County_by_zip[Zip=Zip_of_person]] returns the county for each person, which is logically a left join, but a far simpler and more natural method.
Enable comment auto-refresher