DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
synsog



Group memberships:
Premium Members

Joined: 01 Aug 2010
Posts: 232
Location: Pune
Points: 2274

Post Posted: Mon May 13, 2019 2:40 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Team

i need help in below logic

ID EFF_DT END_DT NR_DAYS DEPT TYPE

1 2018-01-01 2018-01-06 5 A CONTRACT
1 2018-01-07 2018-01-17 10 B CONTRACT
1 2018-01-18 2018-02-17 29 C FT
1 2019-01-01 2019-01-07 6 D CONTRACT
1 2019-01-08 2019-01-17 9 E FT

Out put needed

ID EFF_DT END_DT NR_DAYS DEPT TYPE
1 2018-01-01 2018-01-17 10 B CONTRACT
1 2019-01-01 2019-01-07 6 D CONTRACT

whenever employee is changed FT we need the details such as Min(EFF_DT) when he was in contract and Max(EFF_DT) when he was in contract before becoming FT and Maximum days spent on Dept

was trying with transformer looping any inputs are welcome
mouthou
Participant



Joined: 04 Jul 2004
Posts: 208

Points: 1991

Post Posted: Mon May 13, 2019 1:17 pm Reply with quote    Back to top    

Transformer Looping is an apt choice for this scenario since there is no other direct feature in DS which helps with the result. But if you want to explore the option on the DB side, you could try LEAD() and LAG() functions depending on the type of DB you are using and play with the query usage options as below and fetch the exact record.

Something like

SELECT id, end_dt,
LEAD (end_dt,1) OVER (PARTITION BY id ORDER BY end_dt) AS end_date_cntc
LAG (NR_DAYS,1) OVER (PARTITION BY id ORDER BY end_dt) AS NR_days_cntc
FROM srcdatatable;
......
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 367

Points: 3806

Post Posted: Mon May 13, 2019 9:47 pm Reply with quote    Back to top    

if you can't get the transformer, sort by date take top and bottom each down a link and send to a funnel... but the loop is probably the way to go.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours