The Top/Bottom transform filters the records to contain the top or bottom 'N' number of records as ranked by the columns you select.
The Top/Bottom transform requires one input transform that has at least one column.
For our example, we'll use the output of the Aggregate transform, as seen below:
2. Add the transform
Select the connector link between two transforms.
In the toolbar, choose Insert Other, then Top/Bottom.
To edit/configure the transform, select it and choose Configure in the toolbar.
Steps to configure the Top/Bottom transform:
- Enter a Record Count.
- Choose a Starting Point to select either the records at the Top or Bottom of the result after sorting.
- Uncheck any columns you don't want included in the output.
- Drag and drop the columns from the left that you want used to sort the data, and drop them under Sorting Elements. Data will be sorted first by the first element listed, then by the following elements.
- Click the arrow icon for each sorting element to determine whether to sort by its values Ascending (up arrow) or Descending (down arrow).
Select Use Record Grouping if you want to return the number of records specified above multiple times for groups of records. A list of Grouping Elements will appear: select elements that should have a separate top/bottom list for each of their values.
The figure below illustrates the output from the Top/Bottom transform for the Top 5 records as sorted Descending by OrderQty: