Finding the max value

[Anonymous]

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;

[Digvijay_Singh]

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,

[aarkay29]

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

FirstSortedValue(runningCount,-transDATE2) as presaleTickets

[sunny_talwar]

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;

[jonathandienst]

Or like this:

CURRENTPRESALE3:

LOAD

  FirstSortedValue(eventDATE2, -runningCount) as eventDATE1,

  max(runningCount) as presaleTickets

RESIDENT CURRENTPRESALE2

GROUP BY eventDATE2;

[Anonymous]

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