Get only last suborder

[#1]

Hi folks,

I have an ordertable that contains original orders (-00) and subsequent suborders (-XX), something like this:

4110400

4110401

4110402

4221100

4223300

4224400

4224401

4224402

4224403

or

41104-00

41104-01

41104-02

42211-00

42233-00

42244-00

42244-01

42244-02

42244-03

I want to render the following only loading the maximum of the suborders where exists or the orginalorder where suborderdoesn't exist, with the resulting table:

41104-02

42211-00

42233-00

42244-03

In fact lower numbered suborders are not of interest loading.

How do I go about solving this (scripting this), to render the table with only the highest order suffix ?

Thank you & BR,

David

[swuehl · Accepted solution]

LOAD FirstsortedValue(Test,-subfield(Test,'-',2)) as Test

INLINE [

Test

41104-00

41104-01

41104-02

42211-00

42233-00

42244-00

42244-01

42244-02

42244-03

]

Group by subfield(Test,'-',1)

;

Or in the first case, use something like this:

LOAD FirstsortedValue(Test,-right(Test,2)) as Test

INLINE [

4110400

4110401

4110402

4221100

4223300

4224400

4224401

4224402

4224403

]

Group by left(Test,5)

;

[#3]

Thanks Swuehl!