Functions Utility

Join

by Help

The Join() function combines rows from two 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: MultiJoin

Syntax

Join (Range 1, Range 2, Range 1 Columns, Range 2 Columns, Join Type, Output)

Range 1 The first contiguous range of Excel cells
Range 2 The second contiguous range of Excel cells
Range 1 Columns The column in the first range to join on (match with). This parameter is ignored for cross joins.
Range 2 Columns The column in the second range to join on (match with). This parameter is ignored for cross joins.
Join Type A join type can be “inner”, “outer”, “right”, “left” or “cross”. See more information in the table below.
Output The columns to output

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.
Outer The output contains the data for which a direct match is found as per the match columns specified, and also all unmatched records from both ranges.
Cross The output contains the data for all combinations of both ranges. The match columns can be left empty. If match columns are supplied, they are ignored.

Examples

Example 1. Inner join – Compare holdings of assets common to two accounts

Example 2. Inner join with multiple join keys – Compare holdings of assets common to two accounts by country

Example 3. Left join – Compare holdings and also list Account 1’s assets and holdings for which there is no match

Example 4. Right join – Compare holdings and also list Account 2’s assets and holdings for which there is no match

Example 5. Outer join – Compare holdings and also list all assets and holdings for which there is no match

Example 6. Cross join – Output all combinations of two 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. Inner join – Compare holdings of assets common to two accounts

The Join() function is used to compare holdings for the assets common to both accounts. Therefore the join column is column 2 (C2) from Range 1 (Account 1’s assets) and column 3 (C3) from Range 2 (Account 2’s assets). If no join type is specified, the join function performs an inner join by default.

Range 1 Range 2 Join on asset and compare holdings
Account 1 Asset Holding Account 2 Holding Asset
5060 GMP 800 5090 50000 MBD =Join(A3:C7,D3:F7,”C2″,”C3″,”INNER”,”C2,C3,C5″)
5060 YKG 3000 5090 45000 KVJ
5060 PHO 1300 5090 7000 GMP
5060 BSF 700 5090 15000 YKG
5060 QGW 1200 5090 9500 PHO
Range 1 Range 2 Output
Account 1 Asset Holding Account 2 Holding Asset Asset Account 1 Account 2
5060 GMP 800 5090 50000 MBD GMP 800 7000
5060 YKG 3000 5090 45000 KVJ YKG 3000 15000
5060 PHO 1300 5090 7000 GMP PHO 1300 9500
5060 BSF 700 5090 15000 YKG
5060 QGW 1200 5090 9500 PHO
Download this example

Example 2. Inner join with multiple join keys – Compare holdings of assets common to two accounts by country

The Join() function is used to compare holdings for the assets common to both accounts, per country. Therefore the join column is column 2 (C2) from Range 1 (Account 1’s assets) and column 2 (C2) from Range 2 (Account 2’s assets). There is also a join on column 3 (C3) from Range 1 (Account 1’s investment country) and column 3 (C3) from Range 2 (Account 2’s investment country).

Range 1 Range 2 Join on country and asset and compare holdings
Account 1 Asset Country Holding Account 2 Asset Country Holding
5060 GMP India 800 5090 GMP India 50000 =Join(A3:D7,E3:H7,”C2,C3″,”C2,C3″,”INNER”,”C2,C3,C4,C8″)
5060 GMP Brazil 3000 5090 GMP Russia 45000
5060 PHO China 1300 5090 PHO Lima 7000
5060 PHO Spain 700 5090 PHO China 15000
5060 PHO Ecuador 1200 5090 PHO SPain 9500
Range 1 Range 2 Output
Account 1 Asset Country Holding Account 2 Asset Country Holding Asset Country Account 1 Account 2
5060 GMP India 800 5090 GMP India 50000 GMP India 800 50000
5060 GMP Brazil 3000 5090 GMP Russia 45000 PHO China 1300 15000
5060 PHO China 1300 5090 PHO Lima 7000 PHO Spain 700 9500
5060 PHO Spain 700 5090 PHO China 15000
5060 PHO Ecuador 1200 5090 PHO Spain 9500
Download this example

Example 3. Left join – Compare holdings and also list Account 1’s assets and holdings for which there are no matches

The Join() function is used to compare holdings for the assets common to both accounts. Therefore the join column is column 2 (C2) from Range 1 (Account 1’s assets) and column 3 (C3) from Range 2 (Account 2’s assets). As the join is specified as a left join, the output includes all rows from the first range and the holdings for account 2 where there is a match.

Range 1 Range 2 Join on asset and compare holdings
Account 1 Asset Holding Account 2 Holding Asset
5060 GMP 800 5090 50000 MBD =Join(A3:C7,D3:F7,”C2″,”C3″,”LEFT”,”C2,C3,C6,C5″)
5060 YKG 3000 5090 45000 KVJ
5060 PHO 1300 5090 7000 GMP
5060 BSF 700 5090 15000 YKG
5060 QGW 1200 5090 9500 PHO
Range 1 Range 2 Output
Account 1 Asset Holding Account 2 Holding Asset Asset Account 1 Asset Account 2
5060 GMP 800 5090 50000 MBD GMP 800 GMP 7000
5060 YKG 3000 5090 45000 KVJ YKG 3000 YKG 15000
5060 PHO 1300 5090 7000 GMP PHO 1300 PHO 9500
5060 BSF 700 5090 15000 YKG BSF 700
5060 QGW 1200 5090 9500 PHO QGW 1200
Download this example

Example 4. Right join – Compare holdings and also list Account 2’s assets and holdings for which there are no matches

The Join() function is used to compare holdings for the assets common to both accounts. Therefore the join column is column 2 (C2) from Range 1 (Account 1’s assets) and column 3 (C3) from Range 2 (Account 2’s assets). As the join is specified as a right join, the output includes all rows from the second range and the holdings for account 1 where there is a match.

Range 1 Range 2 Join on asset and compare holdings
Account 1 Asset Holding Account 2 Holding Asset
5060 GMP 800 5090 50000 MBD =Join(A3:C7,D3:F7,”C2″,”C3″,”RIGHT”,”C2,C3,C6,C5″)
5060 YKG 3000 5090 45000 KVJ
5060 PHO 1300 5090 7000 GMP
5060 BSF 700 5090 15000 YKG
5060 QGW 1200 5090 9500 PHO
Range 1 Range 2 Output
Account 1 Asset Holding Account 2 Holding Asset Asset Account 1 Asset Account 2
5060 GMP 800 5090 50000 MBD MBD 50000
5060 YKG 3000 5090 45000 KVJ KVJ 45000
5060 PHO 1300 5090 7000 GMP GMP 800 GMP 7000
5060 BSF 700 5090 15000 YKG YKG 3000 YKG 15000
5060 QGW 1200 5090 9500 PHO PHO 1300 PHO 9500
Download this example

Example 5. Outer join – Compare holdings and also list all assets and holdings for which there are no matches

The Join() function is used to compare holdings for the assets common to both accounts. Therefore the join column is column 2 (C2) from Range 1 (Account 1’s assets) and column 3 (C3) from Range 2 (Account 2’s assets). As the join is specified as an outer join, the output includes rows where there is a match, as well as all rows from both ranges that are unmatched.

Range 1 Range 2 Join on asset and compare holdings
Account 1 Asset Holding Account 2 Holding Asset
5060 GMP 800 5090 50000 MBD =Join(A3:C7,D3:F7,”C2″,”C3″,”OUTER”,”C2,C3,C6,C5″)
5060 YKG 3000 5090 45000 KVJ
5060 PHO 1300 5090 7000 GMP
5060 BSF 700 5090 15000 YKG
5060 QGW 1200 5090 9500 PHO
Range 1 Range 2 Output
Account 1 Asset Holding Account 2 Holding Asset Asset Account 1 Asset Account 2
5060 GMP 800 5090 50000 MBD GMP 800 GMP 7000
5060 YKG 3000 5090 45000 KVJ YKG 3000 YKG 15000
5060 PHO 1300 5090 7000 GMP PHO 1300 PHO 9500
5060 BSF 700 5090 15000 YKG BSF 700
5060 QGW 1200 5090 9500 PHO QGW 1200
MBD 50000
KVJ 45000
Download this example

Example 6. Cross join – Output all combinations of two ranges

The Join() function is used to perform a cross join of two ranges of data. This outputs all combinations of the two ranges.

Range 1 Range 2 Join on asset and compare holdings
Account No Account Asset code Unit Cost
5060 NNN LIFE LIMITED GMP 100 =Join(A3:B5,C3:D7,,,”CROSS”,”C2,C3,C4″)
5000 ABC INSURANCE COMPANY YKG 110
5070 STEELWORKERS PROVIDENT FUND PHO 90
BSF 95
QGW 120
Range 1 Range 2 Output
Account No Account Asset code Unit Cost Account Asset Unit Cost
5060 NNN LIFE LIMITED GMP 100 NNN LIFE LIMITED GMP 100
5000 ABC INSURANCE COMPANY YKG 110 ABC INSURANCE COMPANY GMP 100
5070 STEELWORKERS PROVIDENT FUND PHO 90 STEELWORKERS PROVIDENT FUND GMP 100
BSF 95 NNN LIFE LIMITED YKG 110
QGW 120 ABC INSURANCE COMPANY YKG 110
STEELWORKERS PROVIDENT FUND YKG 110
NNN LIFE LIMITED PHO 90
ABC INSURANCE COMPANY PHO 90
STEELWORKERS PROVIDENT FUND PHO 90
NNN LIFE LIMITED BSF 95
ABC INSURANCE COMPANY BSF 95
STEELWORKERS PROVIDENT FUND BSF 95
NNN LIFE LIMITED QGW 120
ABC INSURANCE COMPANY QGW 120
STEELWORKERS PROVIDENT FUND QGW 120

Download this example

Related Posts

QStringToArray

September 6, 2021

JsonToArray

July 28, 2021

Categories