Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jhl_qv129
Contributor III
Contributor III

Sum x Lowest Values in Script and Group By Two Columns

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;

1 Solution

Accepted Solutions
sunny_talwar

6 Replies
sunny_talwar

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

jhl_qv129
Contributor III
Contributor III
Author

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

    

SchoolConf_RoundConf_ScorePlayerConf_Round_Id4_Low
CSD134Player_11x
CSD134Player_22x
CSD136Player_33x
CSD144Player_44x148
CSD145Player_55
CSD147Player_66
CSD163Player_77
jhl_qv129
Contributor III
Contributor III
Author

Another School example:

       

SchoolConf_RoundConf_ScorePlayerConf_Round_Id4_LowSum 4 Low
PLP136Player_1529x
PLP140Player_1630x
PLP140Player_1731x
PLP141Player_1832x157
PLP142Player_1933
PLP146Player_2034
jhl_qv129
Contributor III
Contributor III
Author

SchoolConf_RoundConf_ScorePlayerConf_Round_Id4_LowSum 4 Low
PLP237Player_1535x
PLP240Player_1736x
PLP243Player_1937x
PLP246Player_2038x146
PLP247Player_1639
PLP249Player_1840
sunny_talwar

jhl_qv129
Contributor III
Contributor III
Author

Brilliant!  Thank you so much!