Wages Computing through Table Joins

Target

Compute the net wages after joining the performance and absence tables to the StandardWages table according to Employee ID.

The original data is stored in 3 Excel sheets: standard, absence and performance as below.

The wage can be computed like this with a simple formula using a join: standardWages*(1+Evaluation-Absence/40)+Bonus.

But traditional spreadsheets need plenty of manual operation to complete the join. Or such formulas：

=IFERROR(INDIRECT(“‘Absence’!”&ADDRESS(MATCH(A2,’Absence’!$A:$A,0),2)),0)

=IFERROR(INDIRECT(“‘Performance’!”&ADDRESS(MATCH(A2,’Performance’!$A:$A,0),2)),0)

=IFERROR(INDIRECT(“‘Performance’!”&ADDRESS(MATCH(A2,’Performance’!$A:$A,0),3)),0)

It is difficult for users to write such complex formulas with conventional spreadsheets. With esCalc, however, manual operation and formula input are not necessary.

1. Import Data

Open standard sheet by copying data from Excel or through wizard, and open the absence and performance sheets in the same way.

2. Set key field

As you can see, the employee ID can be used as the related key field for joining the sheets.

Let’s set cells in EID column of the standard sheet as “master cells”, as shown below .

And you can see the result:

Similarly, set cells in the EID columns of both the absence and performance sheets as “master cells”, as shown below.

Here we have set the key fields for relating the sheets.

3. Perform the join

Firstly, “copy” action:

Then, “join” action:

The result is shown below:

You can see that the performance sheet has been joined to the standard sheet. Similarly, join the absence table to the standard sheet.

With unique integrated model, esCalc is able to perform join and union (including multi-layer grouping mode) on tables without complicated formulas.

4. Compute wages

It’s easy. Just append a column in the standard sheet and enter a formula.

Press Enter and you can get a result as follow:

Unlike traditional spreadsheets, in esCalc, entering the formula just once in a cell will lead to automatic copying of the formula into the other cells at the same business position.

Such smart formula copying is much more convenient with multi-layer grouping.

For more details, please refer to the other cases.