MatchInterval in Straight Table AGGR

[#1]

I have student subject and section wise students’ score from which I have calculated Student and its section wise total score.

Score Cal = sum(if(ScoreMax>=StudentScore and ScoreMin <= StudentScore,Score,0))

StudentID	SectionID	SubjectID	Score
1	1	48	10
1	1	49	20
1	1	45	25
1	1	46	25
1	1	47	25
2	1	46	0
2	1	48	10
2	1	45	25
2	1	47	25
2	1	49	25
3	1	46	0
3	1	49	20
3	1	45	25
3	1	47	25
3	1	48	25

TScore Cal = aggr(sum(if(ScoreMax>=StudentScore and ScoreMin <= StudentScore,Score,0)),StudentID,SectionID,SubjectID)

StudentID	SectionID	TScore
1	1	105
2	1	85
3	1	95

I am done till here.

Now I want to grade the students total score. This is separate table

GradeMin	GradeMax	Grade
0	50	Red
51	79	Yellow
80	120	Green

For Grading I want to compare grade max and grade min with total score to grade him. I am stuck with AGGR. Can anyone help? I have to do everything in straight table

[#2]

What I have done is I have created a dummy table "Grading"

Grading:

load Distinct StudentSectionKey, 1 as GradeLink

Resident Score;

left join(Grading)

LOAD 

     GradeMin,

     GradeMax,

     Grade,

     GradingName

     ,1 as GradeLink

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Grading);

and got this

StudentSectionKey	GradeMin	GradeMax	Grade
1-1	0	50	Red
1-1	51	79	Yellow
1-1	80	120	Green
2-1	0	50	Red
2-1	51	79	Yellow
2-1	80	120	Green
3-1	0	50	Red
3-1	51	79	Yellow
3-1	80	120	Green

Now I am trying to compare this with my total score. But unLuckily I am unable to do so

[PrashantSangle]

Hi,

Did you try with set analysis

Like

only({<TScore={">=GradeMin<=GradeMax"}>}Grade)

You can also try with simple if statement

if(TScore>=GradeMin AND TScore<=GradeMax,Grade)

If your Grade range is always constant then

You can try this also,

IF(TScore>80,'Green',if(TScore>50,'Yellow','Red'))

Regards

[#4]

No its not constant.

if(

aggr(sum(if(ScoreMax>=StudentScore and ScoreMin <=StudentScore,Score,0)),StudentID,SectionID,SubjectID)>=GradeMin

and

aggr(sum(if(ScoreMax>=StudentScore and ScoreMin <=StudentScore,Score,0)),StudentID,SectionID,SubjectID)<=GradeMax

,Grade)

not working

[MK_QSL]

T1:

Load * Inline

[

  StudentID,SectionID,SubjectID,Score

  1,1,48,10

  1,1,49,20

  1,1,45,25

  1,1,46,25

  1,1,47,25

  2,1,46,0

  2,1,48,10

  2,1,45,25

  2,1,47,25

  2,1,49,25

  3,1,46,0

  3,1,49,20

  3,1,45,25

  3,1,47,25

  3,1,48,25

];

T2:

Load

  StudentID,

  SectionID,

  SUM(Score) as TotalScore

Resident T1

Group By StudentID, SectionID;

Drop Table T1;

Grade:

Load * Inline

[

  GradeMin, GradeMax, Grade

  0, 50, Red

  51, 79, Yellow

  80, 120, Green

];

Inner Join (T2)

IntervalMatch(TotalScore)

Load GradeMin, GradeMax Resident Grade;

Left Join (T2) Load * Resident Grade;

Drop Table Grade;

[#6]

Manish have to use Straight table

[MK_QSL]

Provide sample data please...

[PrashantSangle]

Hi,

can you Share sample apps

Regards,

[#9]

I have added

[#10]

I have added