Regional summary with Join and Aggregate

Before you start

This exercise uses two sources: transactions and a customer catalogue. The result must sum only approved transactions by region. Prepare runtime supporting two inputs and Join; generic linear streaming does not execute this graph. Both files must be available through authorized bindings.

Use the small tables in this chapter without downloading old fixture packages. Before designing, verify that customer customer_id is unique: duplicates can multiply joined rows and inflate totals.

Procedure

1. Create a pipe-delimited TXT containing customer_id, amount, status. Set Flat File input to format=txt, delimiter=|, hasHeader=true.
2. Create Excel with a Customers sheet and customer_id,region header. Select that sheet in Excel input, header row 1 and data starting at row 2.
3. Convert customer_id to integer on both branches. On transactions convert amount to number with outputField=amount_number and onError=fail.
4. Clean customer region using trim and upper. Filter transactions to status=APPROVED with Row filter.
5. Join both branches with inner: transactions left, customers right; keys customer_id, keyType=integer, nullsEqual=false. Select the actual identifiers of connected components.
6. Group by region: metric=sum, field=amount_number, outputField=total. Sort total descending and select region,total before CSV output.

Synthetic example

Transactions:

customer_id|amount|status
1|10|APPROVED
1|5|APPROVED
2|7|APPROVED
2|99|REJECTED

Customers sheet rows:

customer_id,region
1,NORTH
2,SOUTH

Expected result:

region,total
NORTH,15.0
SOUTH,7.0

Textual number representation can vary with numeric formatting; verify numeric totals 15 and 7 plus configured order.

Verify the result

Calculate the result independently: ignore REJECTED and sum 10+5 for NORTH and 7 for SOUTH. Expect two rows and exactly two columns. Confirm Join matched all approved identifiers without expansion caused by duplicates. Save and publish the accepted Mapping, configure both Session bindings and run a laboratory Workflow.

Group aggregate returns only grouping keys and metric output; it does not retain the detail of each original transaction.

Troubleshooting

For missing regions, check key types, unmatched data and Join type. For inflated totals, inspect catalogue duplicates. For separate regions caused by spaces or case, check Clean text before aggregation. A streaming error can indicate topology incompatibility rather than bad data. For real amounts, define precision, currency and rounding separately; this example does not establish an accounting policy.