topic Re: MatchInterval in Straight Table AGGR in QlikView

MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:04:19 GMT

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

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:07:22 GMT

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

Re: MatchInterval in Straight Table AGGR

PrashantSangle — Sat, 26 Jul 2014 07:14:51 GMT

Hi,

Did you try with set analysis

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

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:18:44 GMT

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

Re: MatchInterval in Straight Table AGGR

MK_QSL — Sat, 26 Jul 2014 07:19:54 GMT

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;

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:21:10 GMT

Manish have to use Straight table

Re: MatchInterval in Straight Table AGGR

MK_QSL — Sat, 26 Jul 2014 07:22:20 GMT

Provide sample data please...

Re: MatchInterval in Straight Table AGGR

PrashantSangle — Sat, 26 Jul 2014 07:22:49 GMT

Hi,

can you Share sample apps

Regards,

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:27:58 GMT

I have added

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:28:07 GMT

I have added

Re: MatchInterval in Straight Table AGGR

PrashantSangle — Sat, 26 Jul 2014 07:32:44 GMT

Hi,

Did you try Manish's answer.

It should work.

Regards,

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:39:28 GMT

I have to work on front end only. Straight table only! Otherwise it was simple to work with single lines

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:52:16 GMT

any luck?

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 07:55:06 GMT

swuehl Gysbert Wassenaar tresesco

Please help

Re: MatchInterval in Straight Table AGGR

PrashantSangle — Sat, 26 Jul 2014 07:56:49 GMT

Hi,

It is not possible on your front end..

you have to modify your data model.

Regards,

Re: MatchInterval in Straight Table AGGR

Sat, 26 Jul 2014 10:39:49 GMT

Any suggestion on data model. Keeping in view I have to provide sliders for changing grade min and max and also scoring min and max