topic Re: Get only last suborder in QlikView

Get only last suborder

Mon, 15 Jun 2015 11:36:06 GMT

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

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

Re: Get only last suborder

giakoum — Mon, 15 Jun 2015 14:20:52 GMT

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;

Re: Get only last suborder

alexandros17 — Mon, 15 Jun 2015 14:21:19 GMT

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;

Re: Get only last suborder

datanibbler — Mon, 15 Jun 2015 14:22:33 GMT

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

Re: Get only last suborder

sorrakis01 — Mon, 15 Jun 2015 14:27:19 GMT

Hi,

try this

Regards,

Re: Get only last suborder

Mon, 15 Jun 2015 14:32:27 GMT

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

Re: Get only last suborder

giakoum — Mon, 15 Jun 2015 14:36:34 GMT

see attached

Re: Get only last suborder

alexandros17 — Mon, 15 Jun 2015 14:38:15 GMT

Add this code to the one I gave to you ...

Final:
NoConcatenate
LOAD * Resident Temp Where flag = 'isMax';
DROP Table Temp;

Re: Get only last suborder

Tue, 16 Jun 2015 06:08:48 GMT

Thanks Alessandro!