Get the maximum value from a field

[#1]

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

[Anonymous]

Try this:

tablemax:

LOAD distinct NUM_HOTEL, max(INVENTARIO) as MaxINVENTARIO

resident ALL Group By NUM_HOTEL;

[its_anandrjs · Accepted solution]

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,

1,768,

2,0,

2,544

];

MaxTable:

LOAD

NUM_HOTEL,

Max(INVENTARIO) as MaxINVENTARIO

Resident ALL

Group By NUM_HOTEL;

Regards

Anand

[maxgro]

LOAD

     NUM_HOTEL,

     max(INVENTARIO) as Max

resident ALL

Group By

     NUM_HOTEL;

[marcus_sommer]

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

- Marcus

[sunny_talwar]

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,

S

[marcus_sommer]

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

[sunny_talwar]

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,

S

[marcus_sommer]

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

[sunny_talwar]

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,

S