Aggregation and fetch values based on the logic

[skp]

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

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

Output data should be like below

Code: Select all

ID	Cur_Loc	Pre_Loc	Cur_Role	Pre_Role
100	  BAN	  HYD	   SSE	     SE
101	  MUM		        TL	
102	  DEL		        SE	

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.

[chulett]

People, please make use of the Preview option if you are posting something that you expect to look a certain way, like the examples above. Then you can perfect them and make them actually usable rather than making me do that.

The forum software removes all 'extra' whitespace so if you want to preserve it and make loverly little columns, you need to wrap them in code tags. And it won't work like you think it will, so preview / tweak / lather / rinse / repeat until it does.

skp - please confirm that things ended up in the correct column. And let us know what you've tried so far.

[ShaneMuir]

Broadly speaking, you could do the following:
1. Sort your data by the ID and the Date (Desc).
2. Determine when the key field changes.
3. Set up stage variables to capture Cur_Loc, Pre_Loc, Cur_Role, Pre_Role
4. Derivation for each stagevariable would be something like:

Code: Select all

If KeyChange then If Not(IsNull(Input.Row)) then Input.Row else '' else If stagevariable='' then Input.Row else stagevariable 

5. Output the last row in group for the ID.

If your data is sorted correctly then the first record to encountered which has a value in the appropriate column will be inserted and held in the stagevariable until the key changes