max ID
How can I get the max 'subID' for each 'id' and create a second dataset dsB from it.
I need
- 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: 27
- Joined: Mon Jul 11, 2016 12:45 pm
I figured it out.
1 221
2 120
3 325
- 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: 27
- Joined: Mon Jul 11, 2016 12:45 pm
wjblack,
While your solution does work, it can be easier than that:
).
HTH,
Richard
While your solution does work, it can be easier than that:
- Code: Select all
TABLE(dsA,{id,MaxSubID := MAX(GROUP,SubID)},id);

HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
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: 27
- Joined: Mon Jul 11, 2016 12:45 pm
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:
HTH,
Richard
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
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
6 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest