New to QlikView

Discussion board where members can get started with QlikView.

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:

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:

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:

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
MVP

Try attached

6 Replies
MVP

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

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

New Contributor III

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

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

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

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

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

 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
MVP

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

Try attached

New Contributor III

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

Brilliant!  Thank you so much!