Table of Contents

Search

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

0 COMMENTS

We’d like to hear from you!