topic Re: Compare a value from a table to another value from another table in QlikView

Compare a value from a table to another value from another table

maohob1 — Wed, 25 Nov 2020 16:16:04 GMT

Hello all
I am a new user of qlik products

I have a question
I have two tables ( 'StudentTable' and 'GradeTable')

I set up a third table called 'CalculationGradeTable'

Calculates the student's score(SetMark)
Example :
If the student takes the grade of 40, he supposed to have a 'Poor' rating Dependence on 'MinGrade, MaxGrade' field
If the student takes the grade of 88 he should have a 'very good' rating Dependence on 'MinGrade, MaxGrade' field
The valuation should be calculated with the 'SetMark' column

How to do it !!

// My Code ....

StudentTable:
LOAD * INLINE [
IDStd,NameStd,GradeStd
1,'A',40
2,'B',85
3,'C',75
4,'D',45
5,'E',3
6,'F',100
];

GradeTable:
LOAD * INLINE [
MinGrade, MaxGrade,Mark
0, 59 , 'Poor'
60, 75,'good'
76, 89,'very good'
90, 100,'Excellent'
];

CalculationGradeTable:
LOAD
IDStd as ID ,
NameStd as Name ,
IF( GradeStd >= Peek('MinGrade',0,'GradeTable') and
GradeStd <=Peek('MaxGrade',0,'GradeTable')
, Peek('Mark',0 ,'GradeTable'),
IF( GradeStd >= Peek('MinGrade',1,'GradeTable') and
GradeStd <=Peek('MaxGrade',1,'GradeTable')
, Peek('Mark',1 ,'GradeTable'),
IF( GradeStd >= Peek('MinGrade',2,'GradeTable') and
GradeStd <=Peek('MaxGrade',2,'GradeTable')
, Peek('Mark',2 ,'GradeTable'),
IF( GradeStd >= Peek('MinGrade',3,'GradeTable') and
GradeStd <=Peek('MaxGrade',3,'GradeTable')
, Peek('Mark',3 ,'GradeTable')

)))) as SetMark
Resident StudentTable
Order By GradeStd desc ;

Re: Compare a value from a table to another value from another table

Frank_Hartmann — Sun, 20 Jan 2019 13:08:54 GMT

Maybe try like this:

StudentTable:
LOAD * INLINE [
IDStd,NameStd,GradeStd
1,'A',40
2,'B',85
3,'C',75
4,'D',45
5,'E',3
6,'F',100
];


GradeTable:
LOAD * INLINE [
MinGrade, MaxGrade,Mark
0, 59 , 'Poor'
60, 75,'good'
76, 89,'very good'
90, 100,'Excellent'
];

left Join

IntervalMatch(GradeStd)
Final:
LOAD MinGrade,MaxGrade Resident GradeTable;

Re: Compare a value from a table to another value from another table

maohob1 — Sun, 20 Jan 2019 17:15:50 GMT

Great solution.

I have a question, how can I solve the query
Using the for loop and the peek function !!

Re: Compare a value from a table to another value from another table

jonathandienst — Mon, 21 Jan 2019 05:30:40 GMT

I suggest that you use the intervalmatch solution already posted. That would normally be the correct way to solve your problem. Do you have a specific reason not to use interval matching?