1. Use three sort stages. 2. Key sort data in the first sort stage. Keep the sort order ascending. 3. In second sort stage do not key sort data. Set "Sort Key Mode" mode to "Don't Sort, Previously Sorted" and set the "Create Cluster Key Change Column" to TRUE. Keep all other sort stage properties same as first sort stage. 4. Setting "Create Cluster Key Change Column" will give us an extra column with value as 1. 5. Now in the last and third sort column, do not key sort and set "Sort Key Mode" mode to "Don't Sort, Previously grouped". This stage will give you the record with value 1 in the last whereas all other group records with value 0. 6. User transformer stage to filter the first record using the stage variable and constraint to filter the last record from the group.
Refer attached figure to understand the job design.
To implement this design:
ReplyDelete1. Use three sort stages.
2. Key sort data in the first sort stage. Keep the sort order ascending.
3. In second sort stage do not key sort data. Set "Sort Key Mode" mode to "Don't Sort, Previously Sorted" and set the "Create Cluster Key Change Column" to TRUE. Keep all other sort stage properties same as first sort stage.
4. Setting "Create Cluster Key Change Column" will give us an extra column with value as 1.
5. Now in the last and third sort column, do not key sort and set "Sort Key Mode" mode to "Don't Sort, Previously grouped". This stage will give you the record with value 1 in the last whereas all other group records with value 0.
6. User transformer stage to filter the first record using the stage variable and constraint to filter the last record from the group.
Refer attached figure to understand the job design.