topic Re: Get the maximum value from a field in QlikView

Get the maximum value from a field

Tue, 17 Feb 2015 13:01:22 GMT

Hi,

from this table:

ALL:
LOAD * INLINE [
NUM_HOTEL, INVENTARIO,
1,768,
1,768,
2,0,
2,544
];

I would like to have another table with the highest value of INVENTARIO for each value of NUM_HOTEL so the resulting table I'm looking for in this case would have only two rows:

NUM_HOTEL,INVENTARIO

1,768

2,544

How to do this in the easiest way?

Thank you

Re: Get the maximum value from a field

Anonymous — Tue, 17 Feb 2015 13:05:06 GMT

Try this:

tablemax:

LOAD distinct NUM_HOTEL, max(INVENTARIO) as MaxINVENTARIO

resident ALL Group By NUM_HOTEL;

Re: Get the maximum value from a field

its_anandrjs — Tue, 17 Feb 2015 13:05:37 GMT

Hi,

Create another table and there calculate the Maximum values for NUM_HOTEL for each value and then you get single table.

ALL:

LOAD * INLINE [

NUM_HOTEL, INVENTARIO

1,768,

2,0,

2,544

];

MaxTable:

LOAD

NUM_HOTEL,

Max(INVENTARIO) as MaxINVENTARIO

Resident ALL

Group By NUM_HOTEL;

Regards

Anand

Re: Get the maximum value from a field

maxgro — Tue, 17 Feb 2015 13:07:32 GMT

LOAD

NUM_HOTEL,

max(INVENTARIO) as Max

resident ALL

Group By

NUM_HOTEL;

Re: Get the maximum value from a field

marcus_sommer — Tue, 17 Feb 2015 14:08:25 GMT

There is a very interessting alternative available: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook.

- Marcus

Re: Get the maximum value from a field

sunny_talwar — Tue, 17 Feb 2015 14:15:48 GMT

This looks like a nice way to do a max. I am trying to understand what it is doing and how can this be implemented?

LOADmax(FieldValue(‘Id’, recno()))AUTOGENERATEFieldValueCount(‘Id’);

Would you be able to post a sample file with the method that is prescribed in that article? (or may be use the example in this post to show how exactly can it be implemented for this particular scenario.

Thanks,

Re: Get the maximum value from a field

marcus_sommer — Tue, 17 Feb 2015 14:29:44 GMT

It's not more than that - it runs through the field (not through a table) and picked the max. value. This could you the pick with a peek-statement. But it worked only with single fields - if you want the max. value in combination with other fields it's not applicable.

- Marcus

Re: Get the maximum value from a field

sunny_talwar — Tue, 17 Feb 2015 14:35:12 GMT

But how exactly is it determining the max value within a field? and is it locating the max value by another field? For example if I have

Years	ID
2015	1
2015	2
2015	3
2014	4
2014	6

So the above script only going to pull the Max(id) = 6 or can it also give me Max per year (2015 = 3 & 2014 = 6) by manipulating it somehow?

Best,

Re: Get the maximum value from a field

marcus_sommer — Tue, 17 Feb 2015 14:41:57 GMT

Then you will an approach like the correct answer in this posting - sorry, my reply was a mistake because I hadn't read carefully enough to see that the thread was about getting the max. values in combinations/conditions to other fields and not for a single field.

- Marcus

Re: Get the maximum value from a field

sunny_talwar — Tue, 17 Feb 2015 14:45:07 GMT

Thanks Marcus,

Although a mistake, but you actually introduced me to something which I have not seen before. I would definitely want to spend some time on this and learn more about this.

Best,