topic Re: Max Values From Multiple Rows in QlikView

Max Values From Multiple Rows

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max Values From Multiple Rows

sunny_talwar — Fri, 24 Feb 2017 18:56:17 GMT

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;

Re: Max Values From Multiple Rows

sumitjadhav — Sat, 25 Feb 2017 10:06:43 GMT

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: