Sum x Lowest Values in Script and Group By Two Columns

[Anonymous]

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;

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try attached

[Anonymous]

Brilliant!  Thank you so much!