MultiJoin
The MultiJoin() function combines rows from multiple ranges of data into a single range. This function works in a similar way to the join functionality in SQL (structured query language used for querying databases).
Related functions: Join
Syntax
Join (Query, Range 1, Range 2, Range 3 … Range 16)
| Query | The columns to select from one or more ranges, the type of join to perform, and the criteria on which to join ranges |
| Range 1 … Range 16 | The ranges of Excel cells to join together based on the criteria specified by the Query parameter value |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
| Join types | |
| Inner | This is the default setting if no join type is specified. The output contains the data for which a direct match is found as per the match columns specified. |
| Left | The output contains the data for which a direct match is found as per the match columns specified, and also all unmatched records from the first range. |
| Right | The output contains the data for which a direct match is found as per the match columns specified, and also all unmatched records from the second range. |
| QInner | This is a shorthand form of the Inner Join, and columns are not repeated in the output by default – see the example below. |
| How to construct the Query | ||
| Selection |
Select * from Table1 Select Table1.C1, Table1.C2, Table2.C2 from Table1 |
‘*’ refers to all columns Alternatively select specific columns, prefixed with the table name (where Table1 is the 1st input range, Table2 the next etc.)
Note: In either case the word ‘Select’ is optional and can be omitted. |
| Join Type |
Inner Join Table2 on Left Join Table2 on Right Join Table2 on |
Note: There can be repeated joins across a set of multiple tables within a single query |
| Criteria |
Table1.C2 = Table2.C2 Table1.C2 = Table2.C2 and Table1.C3 = Table2.C3 Table1.C2 = Table2.C2 and Table1.C3 < Table2.C3 |
The columns on which to join the ranges Note: Multiple columns can be used to perform the join Valid operators are =, >, <, >= and <= |
Examples
Example 1. Joining two ranges – Inner join
Example 2. Joining two ranges – Left join
Example 3. Joining two ranges – Right join
Example 4. Joining two ranges – Specifying the output
Example 5. Joining two ranges – QInner
Example 6. Joining two ranges on multiple columns
Example 7. Joining two ranges on multiple columns using mixed operators
Example 8. Joining multiple ranges
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Joining two ranges – Inner join
In this example, two sets of prices are joined on a common stock code in order to do a price comparison.
| Range 1 | Range 2 | Join the two ranges | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 55 | =MultiJoin(“Select * from table1 inner join table2 on table1.C1 = table2.C1”,A3:B6,C3:D5) |
| BBB | 60 | CCC | 77 | |
| CCC | 70 | ZZZ | 99 | |
| DDD | 80 | |||
| Range 1 | Range 2 | Output | |||||
| Stock | Price1 | Stock | Price2 | Stock | Price1 | Stock | Price2 |
| AAA | 50 | AAA | 55 | AAA | 50 | AAA | 55 |
| BBB | 60 | CCC | 77 | CCC | 70 | CCC | 77 |
| CCC | 70 | ZZZ | 99 | ||||
| DDD | 80 | ||||||
Example 2. Joining two ranges – Left join
In this example, two sets of prices are joined on a common stock code in order to do a price comparison. All unmatched stocks from the first range are also included in the result.
| Range 1 | Range 2 | Join the two ranges | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 55 | =MultiJoin(“Select * from table1 left join table2 on table1.C1 = table2.C1”,A3:B6,C3:D5) |
| BBB | 60 | CCC | 77 | |
| CCC | 70 | ZZZ | 99 | |
| DDD | 80 | |||
| Range 1 | Range 2 | Output | |||||
| Stock | Price1 | Stock | Price2 | Stock | Price1 | Stock | Price2 |
| AAA | 50 | AAA | 55 | AAA | 50 | AAA | 55 |
| BBB | 60 | CCC | 77 | BBB | 60 | ||
| CCC | 70 | ZZZ | 99 | CCC | 70 | CCC | 77 |
| DDD | 80 | DDD | 80 | ||||
Example 3. Joining two ranges – Right join
In this example, two sets of prices are joined on a common stock code in order to do a price comparison. All unmatched stocks from the second range are also included in the result.
| Range 1 | Range 2 | Join the two ranges | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 55 | =MultiJoin(“Select * from table1 right join table2 on table1.C1 = table2.C1”,A3:B6,C3:D5) |
| BBB | 60 | CCC | 77 | |
| CCC | 70 | ZZZ | 99 | |
| DDD | 80 | |||
| Range 1 | Range 2 | Output | |||||
| Stock | Price1 | Stock | Price2 | Stock | Price1 | Stock | Price2 |
| AAA | 50 | AAA | 55 | AAA | 50 | AAA | 55 |
| BBB | 60 | CCC | 77 | CCC | 70 | CCC | 77 |
| CCC | 70 | ZZZ | 99 | ZZZ | 99 | ||
| DDD | 80 | ||||||
Example 4. Joining two ranges – Specifying the output
In this example, two sets of prices are joined on a common stock code in order to do a price comparison. This is the same as Example 1, but the output is specified such that the join column (C1) is not repeated.
| Range 1 | Range 2 | Join the two ranges, specify the output | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 55 | =MultiJoin(“Select table1.C1,table1.C2,table2.C2 from table1 inner join table2 on table1.C1 = table2.C1”,A3:B6,C3:D5) |
| BBB | 60 | CCC | 77 | |
| CCC | 70 | ZZZ | 99 | |
| DDD | 80 | |||
| Range 1 | Range 2 | Output | ||||
| Stock | Price1 | Stock | Price2 | Stock | Price1 | Price2 |
| AAA | 50 | AAA | 55 | AAA | 50 | 55 |
| BBB | 60 | CCC | 77 | CCC | 70 | 77 |
| CCC | 70 | ZZZ | 99 | |||
| DDD | 80 | |||||
Example 5. Joining two ranges – QInner
This is a shorthand form of an Inner Join. The Join columns are not repeated by default. Note that the output is identical to that of Example 4 above, but the query is much shorter.
Note:
QInner: If no parameters are supplied to QInner, the join is performed on Column 1 from both ranges
QInner(C2): If one column parameter is supplied, that column is used for the join from both ranges
QInner(C2,C3): If two column parameters are supplied, the join is performed on the first column from the first range and the second from the second range
| Range 1 | Range 2 | Join the two ranges | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 55 | =MultiJoin(“QInner”,A3:B6,C3:D5) |
| BBB | 60 | CCC | 77 | |
| CCC | 70 | ZZZ | 99 | |
| DDD | 80 | |||
| Range 1 | Range 2 | Output | ||||
| Stock | Price1 | Stock | Price2 | Stock | Price1 | Price2 |
| AAA | 50 | AAA | 55 | AAA | 50 | 55 |
| BBB | 60 | CCC | 77 | CCC | 70 | 77 |
| CCC | 70 | ZZZ | 99 | |||
| DDD | 80 | |||||
Example 6. Joining two ranges on multiple columns
In this example, two sets of holdings are joined by both stock code and date for comparison purposes.
| Range 1 | Range 2 | |||||
| Stock | Date | Holdings1 | Stock | Date | Holdings2 | |
| AAA | 01-Jan-17 | 11 | AAA | 01-Jan-17 | 100 | =MultiJoin(“Select table1.c1,table1.c2,table1.c3,table2.c3 from table1 inner join table2 on table1.c1 = table2.c1 and table1.c2 = table2.c2”,A3:C8,D3:F8) |
| AAA | 02-Jan-17 | 12 | AAA | 02-Jan-17 | 101 | |
| AAA | 03-Jan-17 | 10 | AAA | 03-Jan-17 | 99 | |
| BBB | 01-Jan-17 | 5 | BBB | 01-Jan-17 | 50 | |
| BBB | 02-Jan-17 | 6 | BBB | 02-Jan-17 | 51 | |
| BBB | 03-Jan-17 | 4 | BBB | 03-Jan-17 | 49 | |
| Range 1 | Range 2 | Join on Column 1 and Column 2 | |||||||
| Stock | Date | Holdings1 | Stock | Date | Holdings2 | Stock | Date | Holdings1 | Holdings2 |
| AAA | 01-Jan-17 | 11 | AAA | 01-Jan-17 | 100 | AAA | 01-Jan-17 | 11 | 100 |
| AAA | 02-Jan-17 | 12 | AAA | 02-Jan-17 | 101 | AAA | 02-Jan-17 | 12 | 101 |
| AAA | 03-Jan-17 | 10 | AAA | 03-Jan-17 | 99 | AAA | 03-Jan-17 | 10 | 99 |
| BBB | 01-Jan-17 | 5 | BBB | 01-Jan-17 | 50 | BBB | 01-Jan-17 | 5 | 50 |
| BBB | 02-Jan-17 | 6 | BBB | 02-Jan-17 | 51 | BBB | 02-Jan-17 | 6 | 51 |
| BBB | 03-Jan-17 | 4 | BBB | 03-Jan-17 | 49 | BBB | 03-Jan-17 | 4 | 49 |
Example 7. Joining two ranges on multiple columns using mixed operators
In this example two sets of prices are joined on a common stock code to assess cases where the first set of prices is higher than the second set of prices.
| Range 1 | Range 2 | Join the two ranges using mixed operators | ||
| Stock | Price1 | Stock | Price2 | |
| AAA | 50 | AAA | 50 | =MultiJoin(“Select table1.c1,table1.c2,table2.c2 from table1 inner join table2 on table1.C1 = table2.C1 and table1.c2 > table2.c2”,A3:B6,C3:D5) |
| BBB | 60 | BBB | 59 | |
| CCC | 70 | CCC | 69 | |
| DDD | 80 | DDD | 80 | |
| EEE | 90 | EEE | 91 | |
| Range 1 | Range 2 | Price1 > Price2 | ||||
| Stock | Price1 | Stock | Price2 | Stock1 | Price1 | Price2 |
| AAA | 50 | AAA | 50 | BBB | 60 | 59 |
| BBB | 60 | BBB | 59 | CCC | 70 | 69 |
| CCC | 70 | CCC | 69 | |||
| DDD | 80 | DDD | 80 | |||
| EEE | 90 | EEE | 91 | |||
Example 8. Joining multiple ranges
In this example, a comparison is made across three sets of prices, all joined on a common stock code.
| Range 1 | Range 2 | Range 3 | Join the three ranges | ||||||
| Stock | Price1 | Stock | Price2 | Stock | Price3 | ||||
| JKL | 60 | GHI | 52 | JKL | 65 | =MultiJoin(“Select table1.C1,table1.C2,table2.C2,table3.C2 from table1 inner join table2 on table1.c1 = table2.c1 inner join table3 on table2.c1 = table3.c1”,A3:B7,C3:D7,E3:F7) | |||
| DEF | 40 | DEF | 42 | DEF | 45 | ||||
| GHI | 50 | ABC | 32 | GHI | 55 | ||||
| ABC | 30 | ABC | 35 | ||||||
| MNO | 70 | ||||||||
| Range 1 | Range 2 | Range 3 | Output | ||||||
| Stock | Price1 | Stock | Price2 | Stock | Price3 | Stock | Price1 | Price2 | Price3 |
| JKL | 60 | GHI | 52 | JKL | 65 | DEF | 40 | 42 | 45 |
| DEF | 40 | DEF | 42 | DEF | 45 | GHI | 50 | 52 | 55 |
| GHI | 50 | ABC | 32 | GHI | 55 | ABC | 30 | 32 | 35 |
| ABC | 30 | ABC | 35 | ||||||
| MNO | 70 | ||||||||