topic Re: Set analysis - Find Max value and other fields in QlikView

Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 16:29:51 GMT

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

Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 19:51:56 GMT

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

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

Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 20:48:04 GMT

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

Re: Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 21:06:45 GMT

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

Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 22:14:12 GMT

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 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

Re: Set analysis - Find Max value and other fields

Thu, 13 Oct 2011 23:44:31 GMT

Hi,

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

Re: Set analysis - Find Max value and other fields

Fri, 14 Oct 2011 00:18:12 GMT

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...

Re: Set analysis - Find Max value and other fields

Sokkorn — Fri, 14 Oct 2011 00:52:30 GMT

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, 3, 31

April, 4, 40];

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

Regards,

Sokkorn