Page 1 of 1

loop and check logic

Posted: Thu Jul 23, 2009 11:33 pm
by dnat
Hi,

We have the data in a table like this

No Column1 Column2
1 M 02-Feb-09
2 L 03-Feb-09
3 L 20-Feb-09
4 L 10-Mar-09
5 H 20-Mar-09
6 M 10-Apr-09

We need to extract the values before H and if the those values of column1 are same (in this case No 4,3 and 2 are same having value L) then extract the value of the oldest of Column2(in this case it is 03-Feb-09..which is No 2)

How can we do that.

I am sure that we do a veritical pivot and take the values as a string.
In this case Column1 values would be MLLLHM. But how to loop through the oldest L and take the date value for that.

Re: loop and check logic

Posted: Fri Jul 24, 2009 5:49 am
by asnrece1
Hi,

I don't know why to specify "before H."
you can take the sort satge on column1 and column2 in descndin order. hen you take remove duplicate stage and mention the key column1 and pass the last record( here yu get the oldest date record).

Here you have the column 1 - M records also there.It applicable to both.

if you get any problem sort data on column2 the convert to date type and do sort.

Thanks

Posted: Fri Jul 24, 2009 6:24 am
by dnat
It is a business rule that we have to take the values before H. The value H or M which i have specified has a significance.

Posted: Fri Jul 24, 2009 6:33 am
by priyadarshikunal
1. Filter out the records you dont want like H and records after that.
2. Sort and then pass only those records having the next records column 1 is same as column 1. you may also need to keep track of count and pass only those records having count =1

Posted: Fri Jul 24, 2009 7:27 pm
by ray.wurlod
Filter stage can do that. WHERE clause supports LIKE operator.

Posted: Mon Jul 27, 2009 11:30 pm
by dnat
This logic had to be applied for every set of records. whatever i gave is for one set of records. So i wrote a routine to handle this.