Join
The Join() function combines rows from two ranges of data into a single range. It works similarly to the join functionality in SQL (Structured Query Language used for querying databases).
Related functions: MultiJoin
Syntax:
Qx.Join(Range 1, Range 2, [Range 1 Columns], [Range 2 Columns], [Join Type], [Output])- In Excel with Quintessence add-in versions prior to 25.1, use:
Join() - In the Quintessence Editor (Studio) using Quintessence language, use:
Join() - In the Quintessence Editor (Studio) using Python language, use:
Qx.Join()
ⓘ See parameter descriptions
| Range 1 | The first contiguous range. |
| Range 2 | The second contiguous range. |
| Range 1 Columns (optional) | The column(s) in the first range to join on. Ignored for cross joins. |
| Range 2 Columns (optional) | The column(s) in the second range to join on. Ignored for cross joins. |
| Join Type (optional) | Specifies the type of join: • Inner • Outer • Left • Right • Cross See examples for details. |
| Output (optional) | Specifies the columns to include in the result. |
Remarks:
- The default parameters are:
- Range 1 Columns:
C1. - Range 2 Columns:
C1. - Join Type:
Inner - Output:
*(all columns).
- Range 1 Columns:
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Inner join with multiple keys
Range1 (A1:D6):
| Account 1 | Asset | Country | Holding |
| 5060 | GMP | India | 800 |
| 5060 | GMP | Brazil | 3000 |
| 5060 | PHO | China | 1300 |
| 5060 | PHO | Spain | 700 |
| 5060 | PHO | Ecuador | 1200 |
Range2 (F1:I6):
| Account 2 | Asset | Country | Holding |
| 5090 | GMP | India | 50000 |
| 5090 | GMP | Russia | 45000 |
| 5090 | PHO | Lima | 7000 |
| 5090 | PHO | China | 15000 |
| 5090 | PHO | Spain | 9500 |
We join on columns 2 and 3 (Asset and Country) from both ranges.
Inner join:
Matches only rows with matching keys.
=Qx.Join(A2:D6,F2:I6,"C2,C3","C2,C3","Inner","C2,C3,C4,C8")Result:
| GMP | India | 800 | 50000 |
| PHO | China | 1300 | 15000 |
| PHO | Spain | 700 | 9500 |
> Example 2: Left join with multiple keys
Range1 (A1:D6):
| Account 1 | Asset | Country | Holding |
| 5060 | GMP | India | 800 |
| 5060 | GMP | Brazil | 3000 |
| 5060 | PHO | China | 1300 |
| 5060 | PHO | Spain | 700 |
| 5060 | PHO | Ecuador | 1200 |
Range2 (F1:I6):
| Account 2 | Asset | Country | Holding |
| 5090 | GMP | India | 50000 |
| 5090 | GMP | Russia | 45000 |
| 5090 | PHO | Lima | 7000 |
| 5090 | PHO | China | 15000 |
| 5090 | PHO | Spain | 9500 |
We join on columns 2 and 3 (Asset and Country) from both ranges.
Left join:
Returns all matched rows plus all unmatched rows from Range1.
=Qx.Join(A2:D6,F2:I6, "C2,C3", "C2,C3", "Left", "C2,C3,C4,C8")Result:
| GMP | India | 800 | 50000 |
| GMP | Brazil | 3000 | |
| PHO | China | 1300 | 15000 |
| PHO | Spain | 700 | 9500 |
| PHO | Ecuador | 1200 |
> Example 3: Right join with multiple keys
Range1 (A1:D6):
| Account 1 | Asset | Country | Holding |
| 5060 | GMP | India | 800 |
| 5060 | GMP | Brazil | 3000 |
| 5060 | PHO | China | 1300 |
| 5060 | PHO | Spain | 700 |
| 5060 | PHO | Ecuador | 1200 |
Range2 (F1:I6):
| Account 2 | Asset | Country | Holding |
| 5090 | GMP | India | 50000 |
| 5090 | GMP | Russia | 45000 |
| 5090 | PHO | Lima | 7000 |
| 5090 | PHO | China | 15000 |
| 5090 | PHO | Spain | 9500 |
We join on columns 2 and 3 (Asset and Country) from both ranges.
Right join:
Returns all matched rows plus all unmatched rows from Range2:
=Qx.Join(A2:D6, F2:I6, "C2,C3", "C2,C3", "Right", "C6,C7,C4,C8")Result:
| GMP | India | 800 | 50000 |
| GMP | Russia | 45000 | |
| PHO | Lima | 7000 | |
| PHO | China | 1300 | 15000 |
| PHO | Spain | 700 | 9500 |
> Example 4: Outer join with multiple keys
Range1 (A1:D6):
| Account 1 | Asset | Country | Holding |
| 5060 | GMP | India | 800 |
| 5060 | GMP | Brazil | 3000 |
| 5060 | PHO | China | 1300 |
| 5060 | PHO | Spain | 700 |
| 5060 | PHO | Ecuador | 1200 |
Range2 (F1:I6):
| Account 2 | Asset | Country | Holding |
| 5090 | GMP | India | 50000 |
| 5090 | GMP | Russia | 45000 |
| 5090 | PHO | Lima | 7000 |
| 5090 | PHO | China | 15000 |
| 5090 | PHO | Spain | 9500 |
We join on columns 2 and 3 (Asset and Country) from both ranges.
Outer join:
Returns all matched rows plus all unmatched rows from both ranges. The IsNullOrEmpty() function is used to ensure that we populate the country and asset whenever it is blank from Range1.
=Qx.Join(A2:D6, F2:I6, "C2,C3", "C2,C3", "Outer", "if(IsNullOrEmpty(C2), C6, C2),if(IsNullOrEmpty(C3), C7, C3),C4,C8")Result:
| GMP | India | 800 | 50000 |
| GMP | Brazil | 3000 | |
| PHO | China | 1300 | 15000 |
| PHO | Spain | 700 | 9500 |
| PHO | Ecuador | 1200 | |
| GMP | Russia | 45000 | |
| PHO | Lima | 7000 |
> Example 5: Cross join with multiple keys
Range1 (A1:D3):
| Account 1 | Asset | Country | Holding |
| 5060 | GMP | India | 800 |
| 5060 | GMP | Brazil | 3000 |
Range2 (F1:I2):
| Account 2 | Asset | Country | Holding |
| 5090 | GMP | India | 50000 |
We join on columns 2 and 3 (Asset and Country) from both ranges.
Outer join:
Combines all rows from both ranges. The match columns can be left empty as they are ignored. We make use of a smaller range in this example for demo purposes only.
=Qx.Join(A2:D3, F2:I2, , , "Cross", "C2,C3,C4,C6,C7,C8")Result:
| GMP | India | 800 | GMP | India | 50000 |
| GMP | Brazil | 3000 | GMP | India | 50000 |