esCalc Operations: Join and Union

When you work with the spreadsheet data, often you need to combine data from multiple sheets. esCalc provides several operations, including join and union, to do this.

1. Join operation  

With esCalc, you can perform the join operation to import data from one esCalc sheet into the other according to the primary key value. To do this, select the cell(s) and copy data in the source sheet, and then select the cell in the target sheet for pasting and click Operation>Join on the right-click context menu to perform a join, which will paste all the homo-cells of the selected cell into the target sheet according to the values of their master cell(s). The shortcut key for the operation is Ctrl+Alt+J.  

Here’s State1.gex containing state information: 

In this table, cells with state abbreviations are set as master cells. For the setting of master cells, please refer to esCac Structure: The Homologous Relationship.

The county population table County1.gex contains data of the 10 counties that have the largest population in 2010. The data has been grouped by states:

The cell holding the state abbreviation in each group row is the master cell.

Suppose you want to import the data from State1.gex, including the state names and the census results, into County1.gex. You can first select the data in the first table – that is, press Ctrl to select A2 and C2 together and then press Ctrl+C or choose the Copy option on the right-click context menu to copy the state names and census results; after that you select the cell for pasting, say C2, in County1.gex and press Ctrl+Alt+J to perform the join. Below is the pop-up Join window:

There are three types of joins: Left join, Full join and Hybrid join. If choosing the default Left join, you’ll get the following result:

As can be seen, the state data from the first cellset has been copied into the second cellset through an equi join according to the state abbreviations contained in both of them. The copying action copies data in all the unhidden homo-cells and the pasting begins from the selected cell in the targeted table in order, without changing the cells’ appearance properties such as alignment style and background color. Since the two columns of copied data in the source table are separated by another column, so they are pasted with one column between them. The result of left join is determined by the state data in the targeted county population table, so the data of the state of Pennsylvania hasn’t been copied because of the absence of the corresponding data from the targeted table; while the state of AZ gets null values because it can’t find corresponding data from the state information table.

After the two tables are joined, you can reorganize data in the new table with formulas. For instance, enter the formula =C2 in A2 to move the state names to column A; then enter =E2 to C2 to copy the population information to column C. You can refer to esCalc Structure: The Smart Copying of Formulas to learn more about esCalc formulas. Finally, select E2 and press Alt+Shift+Delete to delete the population information from all of E2’s homo-cells. The deletion and modification of cell values is covered by esCalc Spreadsheet Editing: Cells. Here’s the reorganized table:

In database, the detailed information of an object is stored in one table. If this object is referenced by another table, it will be recorded by codes. Take the example of the client table that stores the detailed information of all the clients. The client information referenced by the order table and after-sale service table will be referenced only with the client IDs. This storage type is able to effectively reduce the memory usage, as well as to keep data consistency and manage maintenance conveniently. That means to change the client information, change the client table only, rather than all the related tables. On the other hand, this makes the data usage complicated. You need to find the information for clients in the order table, like addresses and the contact ways, from different tables. Yet you can handle the situation with the join operation.

A join operation can work in cellsets with a more complex structure, such as the following state information table State2.gex:

This cellset is different from State1.gex because a region level is added. Cells containing the region name and the state abbreviation are master cells of the rows at the corresponding levels.

The County2_top5.gex table below contains data of 5 counties with the largest population. The data has been grouped by the regions to which the states belong:

To perform a join, select A3 in State2.gex and copy state names, and then select B3 in County2_top5.gex and press Ctrl+Alt+J. This time Full join is checked and here’s the result:

The state names have been pasted into County2_top5.gex according to the corresponding states under the regional levels. The full join will include the regions and states that aren’t in the targeted table and generated empty descendant rows under them according to the structure. These include the state of Florida in the 8^th row and the Northeast New York and Pennsylvania states from the 16^th to 20^th row.

If Hybrid join was checked, the result would be like this:

Different from the pure full join and the left join, the hybrid join performs a full join at the level to which the selected cell belongs and a left join at a higher level. So Florida, along with an empty sub-row, is included under the South, while the Northeast region that has never been in the targeted table is discarded.

2. Union operation

Through a join operation, a table gets data of another table according to the primary key. A union operation, however, combines the same kind of data from two tables. In a source table, select the cell for copying in a certain band and press Ctrl+C to copy data in the cell and all its unhidden homo-cells. In the targeted table, select the cell for pasting and click Operation>Union on the right-click context menu to perform a union, which will create a new homo-band in the targeted table for the copied data according to the value of the data’s master cell and paste the data into the band. A shortcut key Ctrl+Alt+U is also provided.

The union operation matches data according to the selected band’s master cell, as well as the master cell of the band in a higher level at the same time. It will match the master cells beginning from the highest level in the source table with the master cells at every level in the targeted table and union the matching data. The union requires that master cells be set for every level in both the source table and the targeted table.

Below are two county population tables that hold different data. Here’s County_part1.gex:

Unlike County2_top5.gex used in the preceding section, county names, that is, A4 and its homo-cells, in this table are set as master cells for data matching during the union.

The following County_part2.gex contains data of counties whose population ranks are 1, 3, 5, 7 and 9:

Suppose you want to union the county data in County_part2.gex into County_part1.gex. To do so you need to select cells from the county rows, such as B7 and C7, in County_part2.gex to perform the copy action; then select cells for pasting, say C4, in County_part1.gex and press Ctrl+Alt+U to perform the union. Below is the pop-up Union window:

There are three types of unions: Union, All and Inbounds only.

If Union is checked to perform a union, the result will be as follows:

The Union type of union operation will not change the existing bands, such as the records of Cook, Harris, San Diego counties, in County_part1.gex after combining the county data in the source table into the targeted table; will combine the records that County_part1.gex hasn’t into it according to their master values, like the Dallas county in the 8^th row; and will append the records that haven’t their corresponding parent levels in the targeted table at its end, such as the Kings county and its master row of NY state and the higher level row of Northeast region. During the union operation, data will be copied into the targeted table according to the selected cell for pasting. Therefore the population of Dallas and Kings is pasted into column C. Besides, the operation won’t change the appearance properties the cells in the targeted table, which is the same as the join operation.

If the All type was chose:

Then the result of union would be:

The All type of union operation will add the records of the source table according to the values of their master cells at each level into the targeted table as the new homo-records, resulting in bands with the same master cells probably; and will append the records that haven’t their corresponding parent levels in the targeted table at the end of all the other homo-bands.

With the type of Inbounds only, the result will be:

This Inbounds only type of union operation gets a similar result to the result of Union, except that the records of the source table without corresponding parent levels in the targeted table won’t be copied. According to this, the record of Kings county doesn’t not appear in the result as there is no such bands corresponding to New York state and the Northeast region.

3. Merge operation

Apart from the union operation that can combine the “same” type of records together, you can use the merge operation to get the similar result. In a source table, select any cell in a parent row and press Ctrl+C to copy all the unhidden descendant rows of the parent row. Then in a targeted table select any cell of a parent row under which you want to paste the data and choose Operation>Merge on the right-click context menu to perform a merge. The operation will add the copied sub-rows under the targeted parent row according to the value of the master cell of the source parent row. A shortcut key Ctrl+Alt+M is provided.

The two original tables –County_part1.gex and County_part2.gex in the preceding section will be used to help you understand how this operation works. Select any cell from the 5^th row in County_part2.gex and press Ctrl+C to copy data:

Select the 5^th row that holds the South region from County_part1.gex and press Ctrl+Alt+M to perform a merge:

The merge operation has no specific type, so you’ll automatically get the following result:

The data in the South region in the source table has been merged into the South region in the targeted table. The merge operation won’t compare the values the master cells of the two selected master rows, but it will compare those of the each level of descendent rows under them and copied the data that doesn’t exist in the targeted table. So if you want to merge the data of the South region into the West region in the targeted table, the data of Harris and Dallas counties of the state of Texas will be added to the West region.

The merge operation requires that the rows you select in the source table and the targeted table have the same structure of descendant rows, meaning they must be of homo-structure. The concept of homo-structure is detailed by the article esCalc Structure: The Homologous Relationship.

4. Annex operation

esCalc offers the annex operation to append a row, along with its descendant rows, at the end of the table and make it a 0-level row. To do this, you can select any cell in a row and perform Copy and then choose Operation>Annex on the right-click context menu; or you can use the shortcut key Ctrl+Alt+N.

Look at the esCalc sheet County_part1.gex again:

Select any cell from the 8^th row, say B8, and press Ctrl+Alt+N to perform the annex. The operation will copy the data and appearance properties of the 8^th row and its descendant rows and append the rows at the end of 0-level rows. Here’s the result:

By doing so, the appended rows have joined the 0-level band and the 8^th row has become a slave row at level 0. The appearance properties of the cells in these rows remain unchanged. The annex operation has nothing to do with the setting of master cells, yet the setting in the copied band will be kept.

This operation can be performed between different tables as well – that is, you can copy a row and its descendant rows and append them at the end of another esCalc table.