Tue Jun 25, 2019 3:25 am
Login Register Lost Password? Contact Us


max ID

Comments and questions related to the Enterprise Control Language

Sun Apr 14, 2019 10:10 pm Change Time Zone

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
 
Posts: 25
Joined: Mon Jul 11, 2016 12:45 pm

Mon Apr 15, 2019 2:01 am Change Time Zone

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
wjblack
 
Posts: 25
Joined: Mon Jul 11, 2016 12:45 pm

Mon Apr 15, 2019 1:05 pm Change Time Zone

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

Mon Apr 15, 2019 1:17 pm Change Time Zone

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);
wjblack
 
Posts: 25
Joined: Mon Jul 11, 2016 12:45 pm

Mon Apr 15, 2019 1:43 pm Change Time Zone

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

Mon Apr 15, 2019 2:03 pm Change Time Zone

Thanks Richard...
wjblack
 
Posts: 25
Joined: Mon Jul 11, 2016 12:45 pm


Return to ECL

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 1 guest

cron