Set analysis - Find Max value and other fields

[#1]

Hello,

I would appreciate any help wrt the following problem. I have a table as follows:

ColumnA	ColumnB
4	22
4	56
3	18
4	29
4	22

I am trying to do the following:

1. Select the MAX of ColumnA wher ColumnB value is the lowest (i.e. 22)

2. Only return a Single record and not both records where ColumnA is 4 and ColumnB is 22

Thanks in advance.

Regards,

Craig

[#2]

Suggest you take a look at this thread for some pointers ....

http://community.qlik.com/message/120986#120986

[#3]

Hi Jasleen,

Thanks very much for referring me to the link but I am struggling to translate that example into code using the Min and Max functions to solve my problem. I would appreciate it if you could provide me with a sample set analysis statement.

Regards,

Craig

[#4 · Accepted solution]

Hi Craig, here's what I tried .....

[#5]

Thank you very much for your solution and that worked. It did make me wonder though as to how you could solve an extension of the problem as follows:

Col A          Col B          Col C

March          4               22

March          4               31

March          4               22

March          3               38

April            4               31

April            4               31

April            3               31

April            4               40

The objective would be to show for each unique value of Column A:

1. Max of Column B

and 2. Min of Column C

(and only a single unique combination per my original post)

The results would be:

Col A          Col B          Col C

March          4               22

April             4               31

I can post this as a new discussion if needed.

Thanks again and regards,

Craig

[#6]

Hi,

You can use the Min and Max at the load script. Attached the sample QVW  . Hope this rsolves your issue.

[#7]

Craig, check this one out...I withdrew a previous one I attached as I noticed it wasn't giving unique rows by Month. Hope this one works for you...

[Sokkorn]

Hi cschultz,

Try this

LOAD * INLINE [

ColA,          ColB,          ColC

March,        4,               22

March,        4,               31

March,        4,               22

March,        3,               38

April,          4,               31

April,          4,               31

April,          3,               31

April,          4,               40];

Create Straight Table with ColA as Dimension and two Expression MAX(ColB) and MIN(ColC)

Regards,

Sokkorn