Use the join directive to merge rows from two worksheets based on criteria.
The join directive executes an inner join with the "from" worksheet keyed on either the header names they have in common or the arguments, which come in pairs of first the "from" worksheet's header name and then the join worksheet's header name.
Joining worksheets can be used as a powerful alternative to searching. Users can quickly pair two related rows in two different worksheets into a single row in another worksheet.
Joining worksheets can make the processing of related rows in each worksheet simpler and easier to understand.
Consider the following worksheet, Customer and Orders. Here is the Customers worksheet.
And here is the Orders worksheet.
|Order ID||Amount||Customer ID|
Make the CustomerOrders worksheet with this directive declaration:
Note: The join criteria is automatically created when it is not explicitly specified.
In this case, Pebble Stream will assume that the common column named Customer ID is the basis of the join. The joined rows are those that have matching Customer IDs.
|Name||Customer ID||Order ID||Amount|
In this next example, we will explicitly join these two worksheets, Transfers and Taxable, using the criteria we explicitly choose.
Here is the Transfers worksheet.
And here is the Taxable worksheet.
|Account From||Account To||Taxable|
Finally, we obtain Transfers-Taxable by joining the two worksheets using two criteria we explicitly choose.
pebblestream:from(Transfers) pebblestream:join(Taxable, "From", "Account From", "To", "Account To")
In this case we join the two worksheets, wherever the From equals the Account From and the To equals the Account To.
Updated 12 months ago