| |
Join Two Tables
"Join two tables" module is specific in querying tables and performing results like in a database environment. This functionality is usually performed by the vlookup function in Excel. The "Join two tables" module enhances the work on querying tables by presenting an intuitive graphic user interface which makes it an easier process. With no need for writing functions or SQL codes, you can get query results in just a few seconds. If vlookup function is confusing you, then this module will save you time and effort.
There are seven query join types this module can handle:
Download here Gegprifti Add-In for Excel
Documentation
After the installation you should see the GEGPRIFTI tab.
Create the tables you want to join and then click the "Join tables" button to get the following window opened.
Left table:
Select the left table you want to join with.
Left table output columns:
Select the output columns from the left table that will show in the result data. Note that the key column can not be deselected.
By default the first column is set as key column and all columns are set to General data format.
Right click to set a column as key or to format the output column to Text, Number or Date.
Right table:
Select the right table you want to join with.
Right table output columns:
Select the output columns from the right table that will show in the result data. Note that the key column can not be deselected.
By default the first column is set as key column and all columns are set to General data format.
Right click to set a column as key or to format the output column to Text, Number or Date.
Run
Click Run to execute the join tables process and write data results into a new worksheet.
Cancel
Click Cancel to close the Join Tables window.
INNER JOIN
Selects all rows from both tables as long as there is a match between the columns in both tables.
LEFT OUTER JOIN
Selects all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.
RIGHT OUTER JOIN
Selects all rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match.
LEFT OUTER JOIN MINUS THE INNER JOIN
Selects all rows from the left table, with the matching rows in the right table. The result is NULL in the right side when there is no match.
MINUSS,
Rows from both tables as long as there is a match between the columns in both tables.
RIGHT OUTER JOIN MINUS THE INNER JOIN
Selects all rows from the right table, with the matching rows in the left table. The result is NULL in the left side when there is no match.
MINUSS,
Rows from both tables as long as there is a match between the columns in both tables.
NOT INNER JOIN
Selects row from either table when the conditions are met and returns NULL value when there is no match.
MINUS,
Rows from both tables as long as there is a match between the columns in both tables.
FULL OUTER JOIN
Selects row from either table when the conditions are met and return NULL value when there is no match.
Join two tables settings
This window can be opened by clicking the triangle button at the bottom right side of the Database group in GEGPRIFTI ribbon.
Define the null value:
- #N/A (Value Not Available, Default) This is the default configuration. Each time that there is no match between tables the unmatched values will display the #N/A Excel error.
- Blank Each time that there is no match between tables the unmatched values will display blank. Blank cells can be determined by using the =ISBLANK function in Excel.
- Custom null value Specify a custom NULL value each time that there is no match between tables.
Key column matching:
- Case sensitive (Anna ≠ ANNA) Specify if matching key columns should be case sensitive.
- Non case sensitive (Anna = ANNA) Specify if matching key columns should be non case sensitive. This is the default.
Show join type tooltip
If checked the tooltip will popup each time you hand over the mouse at a join type button.
Download here Gegprifti Add-In for Excel
|
|