Field corresponding to max(value)

[#1]

Hi all,

I have a question.

Ths is my database

Model	Color	Sum(Qty)
A	1	10
A	2	15
A	3	3
B	1	6
B	4	10
C	1	4
C	3	8
C	5	2

I would like to have a second table where for each model I see the color with max(Sum(Qty))), i.e.:

Model	Color
A	2
B	4
C	3

where "Model" is the dimension and "Color" is the expression (equals to the color corresponding to max of Sum(Qty) for each model).

Could anyone please help me?

Many thanks!

[#2]

Hi Elena,

please see the attached example.

Good luck!

Rainer

[#3]

hi,

use the following code

tab1:

Model	Color	Sum(Qty)
A	1	10
A	2	15
A	3	3
B	1	6
B	4	10
C	1	4
C	3	8
C	5	2

from ......;

tab2:

load Model,max(Color) resident tab1 group by Model;

thanks

[#4]

Sorry, maybe I was not so clear.

Where I wrote "this is my database" maybe I have to write "this is my straight table" (where "Model" and "Color" are dimensions and "Sum(Qty)" is the expression). Starting from this table I would like to have another one with "Model" as dimension and "Color" as expression (where the value corresponds for each model to the color of max of Sum(Qty)).

Isn't it possible to do it into the expression chart instead of into the script?

[#5]

Hi Elena,

what you need is "FirstSortedValue" as expression.

Good luck!

Rainer