Transformations > Sorter Transformation > Sorter Transformation Example
  

Sorter Transformation Example

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 the 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:
The Mapping Designer shows that in the Properties panel, on the Sort tab, there are two sort conditions defined. The OrderID field and the ItemID field are configured to sort in descending order.
Enable a null treated low sort so that the Mapping Configuration 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:
The Mapping Designer shows that in the Properties panel, on the Advanced tab, the Null Treated Low property is selected.
After the Mapping Configuration 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 Configuration 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:
The mapping shows the data flow from the Product_Orders source to a Sorter transformation. The Sorter Transformation is linked to an Aggregator transformation and then to the Order_Summary target.