topic Field corresponding to max(value) in QlikView

Field corresponding to max(value)

Thu, 27 May 2010 10:50:28 GMT

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!

Field corresponding to max(value)

Thu, 27 May 2010 11:13:39 GMT

Hi Elena,

please see the attached example.

Good luck!

Rainer

Field corresponding to max(value)

Thu, 27 May 2010 11:15:29 GMT

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

Field corresponding to max(value)

Thu, 27 May 2010 11:28:59 GMT

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?

Field corresponding to max(value)

Thu, 27 May 2010 12:06:50 GMT

Hi Elena,

what you need is "FirstSortedValue" as expression.

Good luck!

Rainer