Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Elena,
please see the attached example.
Good luck!
Rainer
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
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?
Hi Elena,
what you need is "FirstSortedValue" as expression.
Good luck!
Rainer