topic Re: Finding the max value in QlikView

Finding the max value

Anonymous — Mon, 13 Mar 2017 20:13:40 GMT

Hello-

I have a data set that looks like this:

eventNAME	eventDATE2	transDATE2	tixPerDay	runningCount
vs. Team A	12/29/2016	9/14/2016	0	16639
vs. Team A	12/29/2016	9/14/2016	-2	16637
vs. Team A	12/29/2016	9/14/2016	5	16642
vs. Team A	12/29/2016	9/14/2016	-2	16640
vs. Team A	12/29/2016	9/15/2016	-2
vs. Team A	12/29/2016	9/15/2016	0
vs. Team A	12/29/2016	9/16/2016	2

I want to write something in my script to identify ONLY the highest runningCount (16640) and the eventDATE2 and then drop the remaining values. Is this possible to do? I have scripting that I'm using to try, but it is not giving me anything close to what it should.

CURRENTPRESALE2:

LOAD

eventNAME,

eventDATE1 as eventDATE2,

transDATE1 as transDATE2,

transQUANTITY as tixPerDay,

If(daysPurchasedFromOnsale < 0, RangeSum(transQUANTITY, Peek('runningCount'))) AS runningCount

RESIDENT CURRENTTICKET2;

//--------THE PART ABOVE THIS IS WORKING FINE AND IS THE DATA TABLE SHOWN ABOVE----------------------

//------- THE PART BELOW IS WHAT IS NOT WORKING ------------------------------------------------//

CURRENTPRESALE3:

LOAD

eventDATE2 as eventDATE1,

max(runningCount) as presaleTickets

RESIDENT CURRENTPRESALE2

GROUP BY eventDATE2;

DROP TABLE CURRENTPRESALE2;

Re: Finding the max value

Digvijay_Singh — Mon, 13 Mar 2017 20:43:30 GMT

Hi,

Looks like code is working fine, if you can share your data sample, I think I can make it work.

In above sample data I don't see value of field daysPurchasedFromOnsale and also may be few data records are not shown which are making running total to large value as 16639.

If you see below script, it shows highest value 3 of running total from the data available in your question, which looks right to me.

CURRENTTICKET2:

Load * inline [

eventNAME, eventDATE1, transDATE1 , transQUANTITY

vs. Team A, 12/29/2016, 9/14/2016, 0

vs. Team A, 12/29/2016, 9/14/2016, -2

vs. Team A, 12/29/2016, 9/14/2016, 5

vs. Team A, 12/29/2016, 9/14/2016, -2

vs. Team A, 12/29/2016, 9/15/2016, -2

vs. Team A, 12/29/2016, 9/15/2016, 0

vs. Team A, 12/29/2016, 9/16/2016, 2

];

CURRENTPRESALE2:

LOAD

eventNAME,

eventDATE1 as eventDATE2,

transDATE1 as transDATE2,

transQUANTITY as tixPerDay,

RangeSum(transQUANTITY, Peek('runningCount')) AS runningCount

RESIDENT CURRENTTICKET2;

Drop table CURRENTTICKET2;

CURRENTPRESALE3:

LOAD

eventDATE2 as eventDATE1,

max(runningCount) as presaleTickets

RESIDENT CURRENTPRESALE2

GROUP BY eventDATE2;

DROP TABLE CURRENTPRESALE2;

Regards,

Re: Finding the max value

aarkay29 — Mon, 13 Mar 2017 20:54:41 GMT

I guess instead of max value you need to use something like this

FirstSortedValue(runningCount,-transDATE2) as presaleTickets

Re: Finding the max value

sunny_talwar — Tue, 14 Mar 2017 04:14:23 GMT

May be you can try this

CURRENTPRESALE2:

LOAD

eventNAME,

eventDATE1 as eventDATE2,

transDATE1 as transDATE2,

transQUANTITY as tixPerDay,

If(daysPurchasedFromOnsale < 0, RangeSum(transQUANTITY, Peek('runningCount'))) AS runningCount

RESIDENT CURRENTTICKET2;

Right Join (CURRENTPRESALE2)

LOAD eventDATE2,

Max(runningCount) as runningCount

Resident CURRENTPRESALE2

Group By eventDATE2;

Re: Finding the max value

jonathandienst — Tue, 14 Mar 2017 04:39:21 GMT

Or like this:

CURRENTPRESALE3:

LOAD

FirstSortedValue(eventDATE2, -runningCount) as eventDATE1,

max(runningCount) as presaleTickets

RESIDENT CURRENTPRESALE2

GROUP BY eventDATE2;

Re: Finding the max value

Anonymous — Tue, 14 Mar 2017 13:59:00 GMT

this yields the same results. Can you please explain the syntax for FirstSortedValue you used?