Getting value from table based on group and max of 2 columns

[mikael-c]

Hello,

I'm quite new at qlik and I must say i'm having a hard time grasping how functions work.

I have a table consisting of 4 columns (see below). I'm trying to get the value, based on the group sector, where the columns date and time are the biggest (max).

sector	date	time	value
s1	10	1	18
s2	10	1	20
s3	10	1	23
s4	10	1	24
s1	10	2	19
s2	10	2	21
s3	10	2	23
s4	10	2	23
s1	10	3	21
s2	10	3	23
s3	10	3	24
s4	10	3	22

This will be inserted into a text box and presented a little like this (results being the max for date and time, sorted by each group )

s1 = 21

s2 = 23

s3 = 24

s4 = 22

In SQL, I would do this either with a window function, with distinct on() or even with a sub-query, but no clue in qlik. Tried playing with aggr() and firstsortedvalue() but not sure where and how to put and order them in expressions.

Thank you!

[stevejoyce · Accepted solution]

This should do it.  There may be an easier/cleaner way to do it but you can try this...

=concat(aggr( if(date= max(Total <sector> date) and time= max(Total <sector> time), sector & ' = ' & max(value)), sector, date, time), chr(13))

[mikael-c]

This is great, thank you so much.
I have to play with this "Total <>" thing to get a hold of the concept.