derive the dates
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
derive the dates
Hi All ,
my source is like this
ID1 ID2 BEG_D END_D PRYR_BEG_D PRYR_END_D
1 1 1970-12-28 2003-10-11 1969-12-29 2002-10-12
2 1 2003-10-05 9999-12-31 2002-10-06 9999-12-31
If the current and previous value of the column ID2 is same I need to build the record set like this
1 st record ID values are same the BEG_D as the PRYR_BEG_D and END_D should be the New record PRYR_BEG_D, I have highlighted the both columns are red
2 nd record ID values are the same (1,1),BEG_D was 2002-10-06 and END_D was previous END_D I have highlighted both columns are green
3rd record will have the same BEG_D and END_D and the ids will be the 1 and 2
My Output Like this
ID1 ID2 BEG_D END_D
1 1 1969-12-29 2002-10-06
1 1 2002-10-06 2003-10-11
2 1 2003-10-05 9999-12-31
Kindly help on this
Thanks
my source is like this
ID1 ID2 BEG_D END_D PRYR_BEG_D PRYR_END_D
1 1 1970-12-28 2003-10-11 1969-12-29 2002-10-12
2 1 2003-10-05 9999-12-31 2002-10-06 9999-12-31
If the current and previous value of the column ID2 is same I need to build the record set like this
1 st record ID values are same the BEG_D as the PRYR_BEG_D and END_D should be the New record PRYR_BEG_D, I have highlighted the both columns are red
2 nd record ID values are the same (1,1),BEG_D was 2002-10-06 and END_D was previous END_D I have highlighted both columns are green
3rd record will have the same BEG_D and END_D and the ids will be the 1 and 2
My Output Like this
ID1 ID2 BEG_D END_D
1 1 1969-12-29 2002-10-06
1 1 2002-10-06 2003-10-11
2 1 2003-10-05 9999-12-31
Kindly help on this
Thanks
Srini
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
then define some variables in a transformer stage to store the current and previous values. and then write output accordingly.
you may need two output link from transformer stage, and collect both records in funnel.
search this forum for how to capture duplicate , use similer logic at transformer stage
you may need two output link from transformer stage, and collect both records in funnel.
search this forum for how to capture duplicate , use similer logic at transformer stage
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 70
- Joined: Thu Nov 09, 2006 2:14 am
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
some hints here:
stage varilable :
prevA=currA
prevb=currb
prevc=currc
prevd=currd
currA=END_D
currB=PRYR_BEG_D
currC=BEG_D
currD=PRYR_END_D
first output link
BEG_D=prevb
END_D=currB
second output link
BEG_D=currB
END_D=PrevA
third output link
BEG_D=currC
END_D=PrevA
and then funnel this three links into one
and then ofcourse you have to set constraints on the links.
stage varilable :
prevA=currA
prevb=currb
prevc=currc
prevd=currd
currA=END_D
currB=PRYR_BEG_D
currC=BEG_D
currD=PRYR_END_D
first output link
BEG_D=prevb
END_D=currB
second output link
BEG_D=currB
END_D=PrevA
third output link
BEG_D=currC
END_D=PrevA
and then funnel this three links into one
and then ofcourse you have to set constraints on the links.