Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I have an ordertable with original orders (-00) and subesequent suborders that are derived from the original order (-XX). Something like this:
4112200
4112201
4112202
4455100
4456000
4667100
4667101
4667102
4667103
or
41122-00
41122-01
41122-02
44551-00
44560-00
46671-00
46671-01
46671-02
46671-03
Now I want to render a table with only the max/last order suffix, in fact I don't want to load the lower numbered orders, rendering a table that would look like:
41122-02
44551-00
44560-00
46671-03
How can I achieve this ?
Thanks
David
Add this code to the one I gave to you ...
Final:
NoConcatenate
LOAD * Resident Temp Where flag = 'isMax';
DROP Table Temp;
split the field into two fields, and then use max and group by to get your result
load
order
max(orderid) as maxorderID
group by
order;
load
subfield(yourfield, '-', 1) as order,
subfield(yourfield, '-', 2) as orderID
resident yourtable;
this is what you need:
MyTable:
LOAD * Inline [
myField
41122-00
41122-01
41122-02
44551-00
44560-00
46671-00
46671-01
46671-02
46671-03
];
Temp: NoConcatenate
LOAD Left(myField,5) as Order, Right(myField,2) as SubOrder Resident MyTable;
Left Join (Temp)
LOAD Order, Max(SubOrder) as SubOrder, 'isMax' as flag Resident Temp Group By Order;
Hi,
if you always have the last two digits to consider, you can split those into a new field and then use the aggregation function max() on those.
Just keep in mind that you need a GROUP BY clause to encompass all othr fields that you have in your table.
HTH
Hi,
try this
Regards,
Hi Alessandro,
I am using the code you presented, but I don't get the wanted result.
The resulting table still doesn't only contain the last suborder in the order series, but contain all orders.
How do I go about excluding the unwanted results in the final table?
Thanks
David
see attached
Add this code to the one I gave to you ...
Final:
NoConcatenate
LOAD * Resident Temp Where flag = 'isMax';
DROP Table Temp;
Thanks Alessandro!