Mapping with Sorter Transformation

by kvt - Comment is Closed

Mapping with Sorter Transformation

# To sort data in ascending or descending order.
# Can sort data from relational or flat file sources.
# The Sorter transformation contains only input/output ports.
# All data passing through the Sorter transformation is sorted according to a sort key.
# The sort key is one or more ports that you want to use as the sort criteria.

Lets create a mapping using Sorter Transformation

Sorter Transformation
Source table used for Sorter Transformation, We have 22 records with 2 NULL and 2 Duplicate first_name with upper and lower case.
Sorter Transformation
Mapping with Sorter Transformation. Here the sort key is First Name and sort type is Ascending order.
Sorter Transformation
Output Target Table – All the 22 records ware sorted by ascending first name. The NULL values are treated as High values by default and hence listed in the last row.
Sorter Transformation
Enable ‘Null Treated Low’ property to treat the NULL value as low value. See the result below.
Sorter Transformation
NULL values are listed in the beginning since its low value because of the property we set in the mapping.
Sorter Transformation
Enable ‘Case Sensitive’ property in Sorter Transformation – To consider case while sorting the record. See result below.
Sorter Transformation
The lower case name ‘dup’ is treated as high value and because of ascending first_name sort type, its pushed to last row. What will happen if we change the sort direction to Descending order. See result below.
Sorter Transformation
See the result now – Sorted with First_name Descending order, Lower case listed first and then the other records and NULL in the last.
Sorter Transformation
Enable ‘Distinct’ property in the sorter transformation. This will automatically consider all ports as part of the sort key. Can you imagine what would be the result? Do you think it will remove one duplicate record and load only 21 record out of 22 ? See result below.
Sorter Transformation
We got all 22 records loaded into target, Why? because ‘case sensitive’ property is also enabled. In that case first_name ‘dup’ & ‘DUP’ are not a duplicate record. so no exclusions.
Sorter Transformation
Here we disabled the ‘Case Sensitive’ property and now see the result below.
Sorter Transformation
Only 21 records were loaded. Why because integration service found one duplicate record (‘dup’ & ‘DUP’) and its discarded.

Related Posts: