Combining rows

[vzeufack]

Hi,

Let's consider we have

Code: Select all

EXPORT layout := RECORD
    INTEGER id;
    INTEGER value1;
    INTEGER value2;
END;

How to convert this:

Code: Select all

id value1 value2
1   10      5
1   20      0
2    5     10
2    5      0
2    0     15

to:

Code: Select all

id value1 value2
1   30      5
2   10     25

Best,

[rtaylor]

vzeufack,

Either the TABLE or ROLLUP functions (both covered in the Intro to ECL Part 2 class) will do that:

Code: Select all

layout := RECORD
  INTEGER id;
  INTEGER value1;
  INTEGER value2;
END;
ds := DATASET([ {1,   10,      5},
                {1,   20,      0},
                {2,    5,     10},
                {2,    5,      0},
                {2,    0,     15}
              ],layout);
ROLLUP(ds,LEFT.id=RIGHT.id,
       TRANSFORM(layout,
                 SELF.value1 := LEFT.value1 + RIGHT.value1,
                 SELF.value2 := LEFT.value2 + RIGHT.value2,
                 SELF := LEFT));
TABLE(ds,
      {id,
       INTEGER value1 := SUM(GROUP,ds.value1),
       INTEGER value2 := SUM(GROUP,ds.value2)},
      id);

HTH,

Richard

[vzeufack]

Thanks very much Richard!

[Allan]

Hi,

There are now YouTube Videos on ROLLUP:
https://www.youtube.com/watch?v=LE5HDegz5II&t=65s
https://www.youtube.com/watch?v=E-R138uuR3M

These might help.
Allan