MultiJoin
The MultiJoin() function combines rows from multiple data ranges into a single range. It works similarly to the JOIN functionality in SQL (Structured Query Language), which is used for querying databases.
Related function: Join
Syntax:
Qx.MultiJoin(Query, Range 1, Range 2, Range 3 … Range 16)- In Excel with Quintessence add-in versions prior to 25.1, use:
MultiJoin() - In the Quintessence Editor (Studio) using Quintessence language, use:
MultiJoin() - In the Quintessence Editor (Studio) using Python language, use:
Qx.MultiJoin()
ⓘ See parameter descriptions
| Query | Specify the columns to select from one or more ranges, the type of join to perform, and the criteria on which to join the ranges. The available join types are: • Inner • Left • Right See examples below. |
| Range n | The ranges to join are determined based on the criteria specified by the Query parameter value. |
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Left and right join on multiple columns
Table1 (A1:B7):
| Stock | Date |
| AAA | 1-Jan-17 |
| AAA | 2-Jan-17 |
| BBB | 1-Jan-17 |
| BBB | 2-Jan-17 |
| CCC | 1-Jan-17 |
| CCC | 2-Jan-17 |
Table2 (D1:F5):
| Stock | Date | Holdings1 |
| AAA | 1-Jan-17 | 100 |
| AAA | 2-Jan-17 | 101 |
| CCC | 1-Jan-17 | 50 |
| DDD | 1-Jan-17 | 20 |
Table3 (H1:J5):
| Stock | Date | Holdings2 |
| AAA | 1-Jan-17 | 200 |
| AAA | 2-Jan-17 | 201 |
| BBB | 1-Jan-17 | 10 |
| CCC | 1-Jan-17 | 45 |
Join sequence and Query:
Left join Table2 to Table1:
Returns all matched rows plus all unmatched rows from Table1 on Stock and Date, returning Stock and Date from Table1 and Holdings1 from Table2:
select
Table1.C1, Table1.C2, Table2.C3
from
Table1
left join
Table2 on Table2.C1 = Table1.C1 and Table2.C2 = Table1.C2Right join Table3 to Table1:
Returns all matched rows plus all unmatched rows from Table3 on Stock and Date, adding Holdings2 to the result:
select
Table1.C1, Table1.C2, Table2.C3, Table3.C3
from
Table1
left join
Table2 on Table2.C1 = Table1.C1 and Table2.C2 = Table1.C2
right join
Table3 on Table3.C1 = Table1.C1 and Table3.C2 = Table1.C2MultiJoin() call:
=Qx.MultiJoin(Query, A2:B7, D2:F5, H2:J5)Result:
| AAA | 1-Jan-17 | 100 | 200 |
| AAA | 2-Jan-17 | 101 | 201 |
| BBB | 1-Jan-17 | 10 | |
| CCC | 1-Jan-17 | 50 | 45 |
> Example 2: Inner join
Table1 (A1:B4):
| Stock | Price |
| AAA | 100 |
| BBB | 200 |
| DDD | 400 |
Table2 (D1:E4):
| Stock | Name |
| AAA | AAA Stock |
| BBB | BBB Stock |
| CCC | CCC Stock |
Query:
select
Table2.C2, Table1.C2
from
Table1
inner join
Table2 on Table2.C1 = Table1.C1This inner join returns only rows where Stock exists in both tables and retrieves the Name from Table1 and Price from Table2.
=Qx.MultiJoin(Query, A2:B4, D2:E4)Result:
| AAA Stock | 100 |
| BBB Stock | 200 |