topic Re: Loading row with max value in QlikView

Loading row with max value

Thu, 22 Oct 2015 13:14:27 GMT

I have fields item, quantity

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

hardware, 7, england

hardware, 9, england

hardware, 10, france

For each item how would I load only the row with the greatest 'quantity' so that my expected output would look like the following :

consumables, 8, brazil

hardware, 10, france

Re: Loading row with max value

pokassov — Thu, 22 Oct 2015 13:16:17 GMT

load

item,

FirstSortedValue(region, -quantity) as region,

max(quantity) as quantity

resident ..

group by item

Re: Loading row with max value

sunny_talwar — Thu, 22 Oct 2015 13:28:55 GMT

You can also try this:

Table:

LOAD item,

quantity,

region

FROM ...;

Right Join (Table)

LOAD item,

Max(quantity) as quantity

Resident Table

Group By item;

Based on your provided sample:

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

Right Join (Table)

LOAD item,

Max(quantity) as quantity

Resident Table

Group By item;

Output:

Re: Loading row with max value

Wed, 28 Oct 2015 16:06:30 GMT

This is really good. I have another question. What if for instance for one of the items there were two rows with identical quantities. So the table looks like

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

consumables, 8, switzerland

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

In this case you still only want to see one of the two rows output for consumables where quantity is 8. Regardless of the region.

Re: Loading row with max value

sunny_talwar — Wed, 28 Oct 2015 16:13:48 GMT

Not sure what you mean? Looking for this output or something else?

Re: Loading row with max value

Wed, 28 Oct 2015 16:17:15 GMT

Hi Sunny ... No effectively just two rows so that for consumables it picks either of the two lines with the greatest quantity. As below :

consumables,8, ( brazil or switzerland)

hardware,10, france

Re: Loading row with max value

sunny_talwar — Wed, 28 Oct 2015 16:24:23 GMT

Try this:

Table:

LOAD * Inline [

item, quantity, region

consumables, 5, spain

consumables, 8, brazil

consumables, 8, switzerland

hardware, 7, england

hardware, 9, england

hardware, 10, france

];

Right Join (Table)

LOAD item,

Max(quantity) as quantity

Resident Table

Group By item;

Right Join(Table)

LOAD quantity,

FirstValue(region) as region

Resident Table

Group By quantity;

Re: Loading row with max value

Wed, 28 Oct 2015 16:51:45 GMT

Sunny this is excellent. Im understanding the pattern of how the joins work now.

If I however had many additional fields for consumables where quantity is equal to 8 - would there be a way to as before only output the two given rows of data irrespective of the additional fields.

Table:

LOAD * Inline [

item, quantity, region, type, name, transport

consumables, 5, spain, indirect, Ben, Air

consumables, 8, brazil, delayed, Alan, Ferry

consumables, 8, spain, direct, Tony, Rail

consumables, 8, switzerland, indirect, Joe, Air

hardware, 7, england, direct, Curtis, Rail

hardware, 9, england, delayed, John, Air

hardware, 10, france, direct, Dave, Ferry

];

So output would be same as before however only FirstValue returned for the additional fields

Re: Loading row with max value

sunny_talwar — Wed, 28 Oct 2015 16:53:26 GMT

Yup you got it right.

Additional rows won't matter. Only the FirstValue (which can be sorted) will be returned.