Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Try attached
Based on the data provided... what is the expected output?
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 |
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 |
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 |
Try attached
Brilliant! Thank you so much!