Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jhl_qv129
New 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

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

6 Replies

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

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

jhl_qv129
New Contributor III

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

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
New Contributor III

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

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
New Contributor III

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

SchoolConf_RoundConf_ScorePlayerConf_Round_Id4_LowSum 4 Low
PLP237Player_1535x
PLP240Player_1736x
PLP243Player_1937x
PLP246Player_2038x146
PLP247Player_1639
PLP249Player_1840

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

jhl_qv129
New Contributor III

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

Brilliant!  Thank you so much!