MAX and MIN dimension values in the same table?

[alematex]

Hi all

I'm trying to replicate this table in a qlikview simple table:

But I don't know how to put the Subject name as a expression.

	max subject	max score	min subject	min score
Student A	English	100	History	75
Student B	History	100	English	81
Student C	Math	92	English	80
Student D	English	98	Math	78

Please somebody help me

[robert_mika]

Can you add jpg with your outcome table

(I'm working on Personal Edition)

[alematex]

The table above is the outcome table the data is

Student A	English	100
Student A	Math	95
Student A	History	75
Student B	English	81
Student B	Math	92
Student B	History	100
Student C	English	80
Student C	Math	92
Student C	History	85
Student D	English	98
Student D	Math	78
Student D	History	80

[Oleg_Troyansky · Accepted solution]

You need to use Advanced Aggregation in order to calculate the subjects with Min and Max scores:

This is the calculation of the max:

MaxString(Aggr(

  if(sum(Score)= max(total <Student> aggr(sum(Score),Student,Subject)), Subject),

Student,Subject))

I recommend MaxString() instead of Only() because there might be 2 subjects with the same scores, and then the formula has to pick one of the two.

If there is only one score per Subject per Student, then perhaps the above expression can be simplified like this:

MaxString(Aggr(

  if(Score= max(total <Student> Score), Subject),

Student,Subject))

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[Anonymous]

See attached

[robert_mika]

t1:

mapping LOAD

Student & ':'& Score as StuS,

Subject

FROM

[151371.xlsx]

(ooxml, embedded labels, table is Sheet1);

t2:

load Student,

max(Score) as max,

min(Score) as min,

applymap('t1',Student& ':' &max(Score)) as maxsubject,

applymap('t1',Student& ':' &min(Score)) as Minsubject

FROM

[151371.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by Student

[alematex]

Thank you very much all answers were correct, and provide me different solutions in different contexts

[anbu1984]

Dimension: Student

Expr: Firstsortedvalue(Subject,-Mark), Max(Mark), Firstsortedvalue(Subject,Mark), Min(Mark)