join

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.

Implicit Join example

Consider the following worksheet, Customer and Orders. Here is the Customers worksheet.

Customer ID

Name

0

John

1

James

2

Alex

And here is the Orders worksheet.

Order ID

Amount

Customer ID

0

5

0

1

1

0

2

8

2

3

6

1

Make the CustomerOrders worksheet with this directive declaration:

pebblestream:from(Customers)
pebblestream:join(Orders)

📘

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

John

0

0

5

John

0

1

1

James

1

3

6

Alex

2

2

8

Explicit Join Example

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.

From

To

Amount

A0

A1

24

A0

A1

576

A2

A1

13

A1

A0

42

And here is the Taxable worksheet.

Account From

Account To

Taxable

A0

A1

TRUE

A0

A2

FALSE

A1

A0

FALSE

A1

A2

TRUE

A2

A0

FALSE

A2

A1

TRUE

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.

Amount

From

To

24

A0

A1

TRUE

576

A0

A1

TRUE

13

A2

A1

TRUE

42

A1

A0

FALSE


Did this page help you?