Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table of values that looks like this:
teamName1 | eventDATE1 | cTimeOut | transQUANTITY | runningCount1 |
vs. TeamX | 9/27/2016 | 0% | 2 | |
vs. TeamX | 9/27/2016 | 0% | -2 | -2 |
vs. TeamX | 9/27/2016 | 0% | -1 | -3 |
vs. TeamX | 9/27/2016 | 0% | -4 | -7 |
vs. TeamX | 9/27/2016 | 0% | 1 | -6 |
vs. TeamX | 9/27/2016 | 0% | 2 | -4 |
vs. TeamX | 9/27/2016 | 0% | 1 | -3 |
vs. TeamX | 9/27/2016 | 0% | -2 | -5 |
vs. TeamX | 9/27/2016 | 8% | 2 | |
vs. TeamX | 9/27/2016 | 8% | 30 | 30 |
vs. TeamX | 9/27/2016 | 8% | -2 | 28 |
vs. TeamX | 9/27/2016 | 8% | 4 | 32 |
vs. TeamX | 9/27/2016 | 8% | -2 | 30 |
vs. TeamX | 9/27/2016 | 17% | 2 | |
vs. TeamX | 9/27/2016 | 17% | 4 | 4 |
vs. TeamX | 9/27/2016 | 17% | -2 | 2 |
vs. TeamX | 9/27/2016 | 17% | 2 | 4 |
vs. TeamX | 9/27/2016 | 17% | 8 | 12 |
vs. TeamX | 9/27/2016 | 17% | -4 | 8 |
vs. TeamX | 9/27/2016 | 17% | 2 | 10 |
vs. TeamX | 9/27/2016 | 17% | 4 | 14 |
vs. TeamX | 9/27/2016 | 17% | 2 | 16 |
What I need to do is extract only the max values from runningCount1 so I'm left with this:
teamName1 | eventDATE1 | cTimeOut | runningCount1 |
vs. TeamX | 9/27/2016 | 0% | -5 |
vs. TeamX | 9/27/2016 | 8% | 30 |
vs. TeamX | 9/27/2016 | 17% | 37 |
The starting point is a table that has been created and dropped numerous times to get it to the the point. There are also thousands of rows of data so rather than a RangeSum which I used earlier - i just want to extract the team, date, time out and highest value.I'm stuck and the following script is giving errors:
CURRENTTICKET7:
LOAD
teamName1,
FirstSortedValue(eventDATE1, teamName1) as eventDATE1,
cTimeOut,
Max(runningCount1) as ticketsSoldPerDay
RESIDENT CURRENTTICKET6;
Any help is appreciated
How is -5 the max value? It seems like it is the last value, but how did you come up with that as the last value? May be try this:
CURRENTTICKET7:
LOAD teamName1,
eventDATE1,
cTimeOut,
LastValue(runningCount1) as ticketsSoldPerDay
RESIDENT CURRENTTICKET6
Group By teamName1, eventDATE1, cTimeOut;
hi scott,
Kindly Try the below Script:
tab1:
LOAD teamName1,
eventDATE1,
cTimeOut,
ransQUANTITY,
runningCount1
FROM
[path);
tab2:
LOAD
teamName1,
eventDATE1,
cTimeOut,
max(runningCount1) as ticketssoldperday,
LastValue(runningCount1) as ticket
Resident tab1
group By
teamName1,
eventDATE1,
cTimeOut;
drop Table tab1;
Out put: