# How do I evaluate values between rows in the transform section?

After running the query, here are the results:

Field1 Field2 Calculated_Field3
AAA 55 0
AAA 66 1
BBB 55 0
BBB 55 0
BBB 66 1

The values in Calculated_Field3 are determined by this logic:
If (Field1 != Next_Row(Field1) ) Then 1 Else 0

Next_Row = The next row value in Field1

This is similar to the LAG/LEAD Oracle function. However, I do not want to use the LAG/LEAD funciton.

Any thougts on how I can accomplish this in the “Transform and Cleanse” or any other way without using the LAG/LEAD function?

"However, I do not want to use the LAG/LEAD funciton."
Why not? You should want to use the LAG/LEAD function. That's the best way.

In any case, here's a way to do it. (I added another row to your sample data).

create table mytable (field1 varchar2 (3), field2 number (2)) ;

insert into mytable (field1, field2) values ('BBB', 55) ;
insert into mytable (field1, field2) values ('CCC', 41) ;
insert into mytable (field1, field2) values ('AAA', 66) ;
insert into mytable (field1, field2) values ('BBB', 66) ;
insert into mytable (field1, field2) values ('AAA', 55) ;
insert into mytable (field1, field2) values ('BBB', 55) ;
commit ;

select a.field1 as f1, a.field2 as f2,
(select min (b.field1) from mytable b
where (b.field1 = a.field1 and b.field2 >= a.field2 and b.rowid != a.rowid)
or (b.field1 > a.field1)
) as nxt
from mytable a
order by a.field1, a.field2 ;

F1 F2 NXT

AAA 55 AAA
AAA 66 BBB
BBB 55 BBB
BBB 55 BBB
BBB 66 CCC
CCC 41

Using the NXT field to derive the value of the requested Calculated_Field3 is left as an exercise to the alert reader.

We will look into having these functions as a part of our Data Cleansing suite. Create CR 109331.

Thank you Ryan. When dealing with large data set and queries that take long to return values, it is best to do analytic locally after fetching the raw data.