loop and check logic

[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.

[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

[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.

[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

[ray.wurlod]

Filter stage can do that. WHERE clause supports LIKE operator.

[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.