Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

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 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:
On the Sorter transformation Sort tab, the OrderID field and the ItemID field are 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:
On the Sorter transformation Advanced tab, the Null Treated Low property is selected. The other properties are set to the default values.
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:
The mapping shows the data flow from the source to a Sorter transformation. The Sorter transformation is linked to an Aggregator transformation and then to the target.