| |
Join Multiple Tables
"Join multiple tables" module is specific in querying multiple tables and performing results like in a database environment. This functionality is usually performed by the vlookup function in Excel. The "Join multiple 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
After the installation you should see the GEGPRIFTI tab.
Create the tables you want to join and then click the "Join multiple tables" button to get the following window opened.
JOIN MULTIPLE TABLES
Left table key columns:
Select the left table key columns you want to join with.
Join types:
Select the join type. The join type is used to join the left table with the right table based on the specified key columns per row. The default value is "INNER JOIN" For more information on join types click "help on join types".
Right table key columns:
Select the right table key columns you want to join with.
Adding a new join row:
After all fields are completed ("Left table key column" and "Join type" and "Right table key column") a new row is added automatically.
Remove a join row:
A join row can be removed by clicking the "X" button on the right side of the row. Note that only the last compleded row can be removed.
Help on join types:
This will open a new window with the information about the join types.
OUTPUT COLUMNS
Show:
Select the output columns to be displayed in the result data. By default all collumns are selected.
Info:
Indicates if a column is a key column or not.
Table.Column:
Column name in the form of Table.Column.
Data type formating:
Select the data type formating for the output resul data. By default "General" is selected. Data type options are as below:
- General (Excel cells are preformated with General data type)
- Text (Excel cells are preformated as Text)
- Integer (Excel cells are preformated as Integer)
- Decimal (Excel cells are preformated as Decimal with two digits after the decimal point)
- Date time (Excel cells are preformated as Date time "yyyy-mm-dd hh:mm:ss AM/PM")
- Date (Excel cells are preformated as Date "yyyy-mm-dd")
- Time (Excel cells are preformated as Time "hh:mm:ss AM/PM")
Output unique rows:
If selected then only distinct(unique) rows are exported into a new worksheet.
Run
Click Run to execute the join multiple tables process and write data results into a new worksheet.
Cancel
Click Cancel to close the Join multiple tables window.
HELP ON KEYBOARD SHORTCUTS
CTRL + PgUp - Switches between worksheet tabs, from left-to-right.
CTRL + PgDn - Switches between worksheet tabs, from right-to-left.
CTRL + LEFT ARROW - Scroll to the left by one column.
CTRL + RIGHT ARROW - Scroll to the right by one column.
CTRL + UP ARROW - Scroll to the left by one row.
CTRL + DOWN ARROW - Scroll to the right by one row.
CTRL + HOME - Scroll to the beginning of a worksheet.
HELP ON JOIN TYPES
INNER JOIN or (≈ INNER JOIN ≈ approximate match)
Selects all rows from both tables as long as there is a match between the columns in both tables.
LEFT JOIN or (≈ LEFT JOIN ≈ approximate match)
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 JOIN or (≈ RIGHT JOIN ≈ approximate match)
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 JOIN MINUS THE INNER JOIN or (≈ LEFT JOIN MINUS THE INNER JOIN ≈ approximate match)
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 JOIN MINUS THE INNER JOIN or (≈ RIGHT JOIN MINUS THE INNER JOIN ≈ approximate match)
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 or (≈ NOT INNER JOIN ≈ approximate match)
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 JOIN or (≈ FULL JOIN ≈ approximate match)
Selects row from either table when the conditions are met and return NULL value when there is no match.
Join multiple 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
Not used in "Join multiple tables".
Download here Gegprifti Add-In for Excel
|
|