You need to create an invoice for customer sales from a customer database. Use a Sorter transformation on the customer sales object to sort the data in ascending order according to the order number. Use the result of the Sorter transformation as an input to the Aggregator transformation. You can increase Aggregator transformation performance with the sorted incoming fields option.
The Product_Orders table contains information about all the orders placed by customers.
OrderID
ItemID
Item
Quantity
Price
43
123456
ItemA
3
3.04
41
456789
ItemB
2
12.02
43
000246
ItemC
6
34.55
45
000468
ItemD
5
0.56
43
NULL
ItemE
1
0.75
41
123456
ItemA
4
3.04
45
123456
ItemA
5
3.04
45
456789
ItemB
3
12.02
In the Mapping Designer, add Product_Orders as a source object.
Add a Sorter transformation to the mapping canvas, and connect it to the data flow. Sort the product orders by order ID and item ID.
The following image shows a sort condition with the order ID and item ID fields configured to sort in descending order:
Enable a null treated low sort so that the mapping task considers null values to be lower than other values.
The following image shows the advanced properties for the Sorter transformation, with the Null Treated Low option selected:
After the mapping task sorts the data, it passes the following rows out of the Sorter transformation:
OrderID
ItemID
Item
Quantity
Price
45
456789
ItemB
3
12.02
45
123456
ItemA
5
3.04
45
000468
ItemD
5
0.56
43
123456
ItemA
3
3.04
43
000246
ItemC
6
34.55
43
NULL
ItemE
1
0.75
41
456789
ItemB
2
12.02
41
123456
ItemA
4
3.04
You need to find out the total amount and the item quantity for each order. You can use the result of the Sorter transformation as an input to an Aggregator transformation to increase performance. Add the Aggregator transformation to the mapping, and connect the transformation to the data flow. Group the fields in the Aggregator transformation by the Order ID, and add an expression to sum the orders by price.
When you pass the data from the Sorter transformation, the Aggregator transformation groups the order ID to calculate the total amount for each order.
OrderID
Sum
45
54.06
43
217.17
41
36.2
Write the order summaries to an object in the data warehouse that stores all order totals.
The mapping task reads the orders data from the source, sorts the orders, totals the prices, and then writes the data to the target.
The following image shows the mapping of the data flow:
