derive the dates

uppalapati2003 · Post by **uppalapati2003** » Fri Aug 01, 2008 7:42 am

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

keshav0307 · Post by **keshav0307** » Fri Aug 01, 2008 7:59 am

are there any chance there can be three records with the same value on column ID2. Like

1 1 1970-12-28 2003-10-11 1969-12-29 2002-10-12
2 1 2001-10-06 2002-10-06 2002-10-06 2002-10-12
3 1 2003-10-05 9999-12-31 2002-10-06 9999-12-31

uppalapati2003 · Post by **uppalapati2003** » Fri Aug 01, 2008 8:12 am

No, that would not happen

keshav0307 · Post by **keshav0307** » Fri Aug 01, 2008 8:21 am

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

uppalapati2003 · Post by **uppalapati2003** » Fri Aug 01, 2008 8:32 am

Hello,

I have the idea to remove the duplicates using the transformer
but the problem is to get the date values of all records
if u have any idea kindly suggest me how to derive the dates

Thanks

keshav0307 · Post by **keshav0307** » Fri Aug 01, 2008 8:41 am

in the second record, you can have all the dates in 1st and 2nd row in parameters, then do all the calculation.

uppalapati2003 · Post by **uppalapati2003** » Fri Aug 01, 2008 8:45 am

Could u please explain breifly, i am unable to get u

thanks

keshav0307 · Post by **keshav0307** » Fri Aug 01, 2008 8:56 am

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.