Difference between adjacent rows?
Good Morning,
I am trying to figure out how to take the difference between two adjacent rows in a dataset. What I have tried is as follows:
1. make transform function such that new.column = R.column-L.column
2. iterate the function on the dataset.
so for example, given a dataset:
col1 col2
row1 2 3
row2 5 7
row3 7 7
then if i run the iterate function, would like to get the following results.
col1 col2
row1 0 0
row2 3 4
row3 2 0
Is there a nice way to do this? Appreciate any help...
I am trying to figure out how to take the difference between two adjacent rows in a dataset. What I have tried is as follows:
1. make transform function such that new.column = R.column-L.column
2. iterate the function on the dataset.
so for example, given a dataset:
col1 col2
row1 2 3
row2 5 7
row3 7 7
then if i run the iterate function, would like to get the following results.
col1 col2
row1 0 0
row2 3 4
row3 2 0
Is there a nice way to do this? Appreciate any help...
- jcma
- Posts: 5
- Joined: Fri Dec 11, 2015 3:36 pm
Here is the nicest way I can think of doing this:
Regards,
Bob
- Code: Select all
/* Given a dataset:
col1 col2
row1 2 3
row2 5 7
row3 7 7
then if i run the iterate function, would like to get the following results.
col1 col2
row1 0 0
row2 3 4
row3 2 0
*/
MyRec := RECORD
INTEGER2 Value1;
INTEGER2 Value2;
END;
SomeFile := DATASET([{2,3},{5,7},{7,7}],MyRec);
TempRec := RECORD
SomeFile.Value1;
SomeFile.Value2;
INTEGER2 Value3 := 0;
INTEGER2 Value4 := 0;
END;
BaseRec := DATASET([{0,0,0,0}],TempRec);
TempTbl := TABLE(SomeFile,TempRec);
TempRec SubThem(TempRec Le, TempRec Ri) := TRANSFORM
SELF.value1 := Ri.Value1-Le.Value3;
SELF.Value2 := Ri.Value2-Le.Value4;
SELF.Value3 := Ri.Value1;
SELF.Value4 := Ri.Value2;
END;
AddedRecs := ITERATE(TempTbl,SubThem(LEFT,RIGHT));
output(BaseRec+AddedRecs[2..],{Value1,value2});
Regards,
Bob
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
jcma,
ITERATE won't work in this case, since you want to compare the original prior record to each subsequent record and produce a new recordset. What you want to use is the PROCESS function (it's an "iterate on steroids" function), like this:
HTH,
Richard
ITERATE won't work in this case, since you want to compare the original prior record to each subsequent record and produce a new recordset. What you want to use is the PROCESS function (it's an "iterate on steroids" function), like this:
- Code: Select all
rec := RECORD
INTEGER Col1;
INTEGER Col2;
END;
ds := DATASET([{2,3},{5,7},{7,7}],rec);
rec DSxform(rec L,Rec R) := TRANSFORM
SELF.Col1 := L.Col1 - R.Col1;
SELF.Col2 := L.Col2 - R.Col2;
END;
p := PROCESS(ds,
ds[1],
DSxform(LEFT,RIGHT),
TRANSFORM(LEFT));
OUTPUT(p);
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
I like Richard's solution better, I did not realize that the Row Transform in the PROCESS could be done inline 
The beautiful thing about ECL is that there are many ways to get to the mountain top!
Thanks Richard!
Bob

The beautiful thing about ECL is that there are many ways to get to the mountain top!
Thanks Richard!
Bob
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
Thank you both for your help, I didn't realize we had the PROCESS function. Maybe should have paid attention better during ECL training
.
Cheers,
James

Cheers,
James
- jcma
- Posts: 5
- Joined: Fri Dec 11, 2015 3:36 pm
5 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest