Aggregation and fetch values based on the logic
Posted: Wed May 04, 2016 6:44 am
Hi All,
I have requirement that group by ID field fetch the value from Current Location/Current Role based on the Maximum date, if value is blank for the maximum date then fetch the previous date value and so on. And fetch the value from Previous Location/Previous Role based on Minimum date, if value is blank for minimum date then fetch the next date value and so on.
Below is Source data
Output data should be like below
Note: Same Employee ID can have multiple entries but above logic should apply to fetch the records.
Any one can suggest how we can implement this logic in datastage.
I have requirement that group by ID field fetch the value from Current Location/Current Role based on the Maximum date, if value is blank for the maximum date then fetch the previous date value and so on. And fetch the value from Previous Location/Previous Role based on Minimum date, if value is blank for minimum date then fetch the next date value and so on.
Below is Source data
Code: Select all
ID Cur_Loc Pre_Loc Cur_Role Pre_Role Date
100 HYD SE 01-APR-16
100 BAN HYD SSE SE 02-APR-16
100 BAN HYD SSE 20-APR-16
101 MUM TL 25-APR-16
102 DEL SE 29-APR-16
Code: Select all
ID Cur_Loc Pre_Loc Cur_Role Pre_Role
100 BAN HYD SSE SE
101 MUM TL
102 DEL SE
Any one can suggest how we can implement this logic in datastage.