Difference between adjacent rows?

[jcma]

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

[bforeman]

Here is the nicest way I can think of doing this:

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

[rtaylor]

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:

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);

PROCESS is similar to ITERATE, but it allows you to specify the record that each record in the dataset is "banged" against.

HTH,

Richard

[bforeman]

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

[jcma]

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