Wed Dec 12, 2018 10:17 pm
Login Register Lost Password? Contact Us


Difference between adjacent rows?

Topics specific to using ECL from a Data Analyst standpoint

Fri Dec 11, 2015 3:40 pm Change Time Zone

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...
jcma
 
Posts: 5
Joined: Fri Dec 11, 2015 3:36 pm

Fri Dec 11, 2015 7:39 pm Change Time Zone

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
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 978
Joined: Wed Jun 29, 2011 7:13 pm

Fri Dec 11, 2015 7:39 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1396
Joined: Wed Oct 26, 2011 7:40 pm

Fri Dec 11, 2015 7:48 pm Change Time Zone

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
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 978
Joined: Wed Jun 29, 2011 7:13 pm

Fri Dec 11, 2015 7:50 pm Change Time Zone

Thank you both for your help, I didn't realize we had the PROCESS function. Maybe should have paid attention better during ECL training :D .

Cheers,
James
jcma
 
Posts: 5
Joined: Fri Dec 11, 2015 3:36 pm


Return to ECL for Analysts

Who is online

Users browsing this forum: No registered users and 1 guest