max ID

[wjblack]

How can I get the max 'subID' for each 'id' and create a second dataset dsB from it.

Code: Select all

LayoutA := RECORD
  unsigned8 id;
  integer8 subId;
END;

LayoutB := RECORD
  unsigned8 id;
  integer8 subId;
END;

dsA := DATASET([ {2,'100'},
                {1,'111'},
                {2,'120'},
                        {1,'221'},
                        {3,'100'},
                        {3,'325'}],LayoutA);


I need

Code: Select all

dsA := DATASET([{1,'221'},
                {2,'120'},
                {3,'325'}],LayoutB);


[wjblack]

I figured it out.

Code: Select all

dsB := rollup(sort(dsA,id),
                     left.id = right.id,
                     transform(LayoutB,
                                    self.subId := if(left.id=right.id, max(left.subId,right.subId),right.subId);
                                    self := left
                                    ),
                    local);


1 221
2 120
3 325

[rtaylor]

wjblack,

While your solution does work, it can be easier than that:

Code: Select all

TABLE(dsA,{id,MaxSubID := MAX(GROUP,SubID)},id);

A simple Crosstab report (covered in the Intro to ECL class, part 2 ).

HTH,

Richard

[wjblack]

This is for my initial solution and not Richards. Better yet this works better and is less resource intensive.

Code: Select all

dsB := dedup(sort(dsA,id,-subId),id);

[rtaylor]

wjblack,

Yes, that is better than the ROLLUP solution.

And, if your end result would be to JOIN my TABLE result back to your dsA dataset to get the rest of the fields in that max subID record, then this would also be better than that one, too, because it eliminates the JOIN and would get you straight to the result you wanted). Like this:

Code: Select all

LayoutA := RECORD
  unsigned8 id;
  integer8 subId;
   STRING    stuff;
END;

dsA := DATASET([{2,'100','A'},
                {1,'111','B'},
                {2,'120','C'},
                {1,'221','D'},
                {3,'100','E'},
                {3,'325','F'}],LayoutA);
                                    
// tbl := TABLE(dsA,{id,MaxSubID := MAX(GROUP,SubID)},id);
// dsB := JOIN(dsA,tbl,
//             LEFT.ID = RIGHT.ID AND LEFT.subID = RIGHT.MaxsubID,
//             TRANSFORM(LEFT) );
dsB := DEDUP(SORT(dsA,id,-subId),id); //eliminates the JOIN

dsB;

HTH,
Richard

[wjblack]

Thanks Richard...