topic Re: MAX and MIN dimension values in the same table? in QlikView

MAX and MIN dimension values in the same table?

alematex — Mon, 09 Feb 2015 21:26:23 GMT

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

Re: MAX and MIN dimension values in the same table?

robert_mika — Mon, 09 Feb 2015 22:51:13 GMT

Can you add jpg with your outcome table

(I'm working on Personal Edition)

Re: MAX and MIN dimension values in the same table?

alematex — Mon, 09 Feb 2015 22:55:55 GMT

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

Re: MAX and MIN dimension values in the same table?

Oleg_Troyansky — Mon, 09 Feb 2015 23:05:22 GMT

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!

Re: MAX and MIN dimension values in the same table?

Anonymous — Mon, 09 Feb 2015 23:19:56 GMT

See attached

Re: MAX and MIN dimension values in the same table?

robert_mika — Tue, 10 Feb 2015 00:00:29 GMT

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

Re: MAX and MIN dimension values in the same table?

alematex — Tue, 10 Feb 2015 00:29:36 GMT

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

Re: MAX and MIN dimension values in the same table?

anbu1984 — Tue, 10 Feb 2015 02:49:38 GMT

Dimension: Student

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