topic Re: Sum x Lowest Values in Script and Group By Two Columns in QlikView

Sum x Lowest Values in Script and Group By Two Columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to sum the 4 lowest values in the script AND group it by two columns..

So for each School, I want the sum of the 4 lowest Conf_Score per Conf_Round

Not sure the QV attachment will work here as it is Personal edition, but am attaching raw data and including script:

Conference_Data:

LOAD School,

Conf_Round,

Conf_Score,

Player,

Conf_Round_Id,

[4_Low] //excel manual flag, I want to calculate in Script: sum of 4 lowest Conf_score, per School and Conf_Round

FROM

[\\Mac\Home\Desktop\Golf\Conference Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Player_Stats:

LOAD

Player,

Sum(Conf_Score)/Count(Conf_Round_Id) as Player_Avg,

Count(Conf_Score) as Player_Total,

Min(Conf_Score) as Player_Low,

Max(Conf_Score) as Player_Max,

Count(Conf_Round_Id) as Player_Rounds

Resident Conference_Data

Group By Player;

School_Average:

LOAD

School,

Sum(Conf_Score)/Count(Conf_Round_Id) as School_Avg,

Sum(Conf_Score) as School_Total,

Min(Conf_Score) as School_Min,

Max(Conf_Score) as School_Max,

Count(Conf_Round_Id) as School_Rounds

Resident Conference_Data

Group By School;

Re: Sum x Lowest Values in Script and Group By Two Columns

sunny_talwar — Fri, 09 Mar 2018 02:04:07 GMT

Based on the data provided... what is the expected output?

Re: Sum x Lowest Values in Script and Group By Two Columns

Anonymous — Fri, 09 Mar 2018 02:35:57 GMT

Thank you for your reply. For example:

The School "CSD" has 7 "Player" in "Conf_Round = 1"

The Sum of the 4 lowest Conf_Score for this School and Conf_Round is 148

I am looking for Sum of the 4 lowest Conf_Score: Per School and Conf_Round.

So, For School = CSD and Conf_Round = 1, output is 148

School	Conf_Round	Conf_Score	Player	Conf_Round_Id	4_Low
CSD	1	34	Player_1	1	x
CSD	1	34	Player_2	2	x
CSD	1	36	Player_3	3	x
CSD	1	44	Player_4	4	x	148
CSD	1	45	Player_5	5
CSD	1	47	Player_6	6
CSD	1	63	Player_7	7

Re: Sum x Lowest Values in Script and Group By Two Columns

Anonymous — Fri, 09 Mar 2018 02:42:30 GMT

Another School example:

School	Conf_Round	Conf_Score	Player	Conf_Round_Id	4_Low	Sum 4 Low
PLP	1	36	Player_15	29	x
PLP	1	40	Player_16	30	x
PLP	1	40	Player_17	31	x
PLP	1	41	Player_18	32	x	157
PLP	1	42	Player_19	33
PLP	1	46	Player_20	34

Re: Sum x Lowest Values in Script and Group By Two Columns

Anonymous — Fri, 09 Mar 2018 02:44:10 GMT

School	Conf_Round	Conf_Score	Player	Conf_Round_Id	4_Low	Sum 4 Low
PLP	2	37	Player_15	35	x
PLP	2	40	Player_17	36	x
PLP	2	43	Player_19	37	x
PLP	2	46	Player_20	38	x	146
PLP	2	47	Player_16	39
PLP	2	49	Player_18	40

Re: Sum x Lowest Values in Script and Group By Two Columns

sunny_talwar — Fri, 09 Mar 2018 10:16:49 GMT

Try attached

Re: Sum x Lowest Values in Script and Group By Two Columns

Anonymous — Fri, 09 Mar 2018 13:35:25 GMT

Brilliant! Thank you so much!