topic Re: If Then Condition in QlikView

If Then Condition

tracycrown — Sat, 16 Nov 2024 17:57:32 GMT

Dear all

This is a simple problem but not sure why I am unable to get the correct answer.

Thank you, Tracy

MK_QSL — Sat, 17 Oct 2020 09:31:41 GMT

Try something like below...

Result:
LOAD Year,
Student,
Subject,
Score
FROM
[Test-1.xlsx]
(ooxml, embedded labels, table is Result);

Grade:
LOAD Grade,
Lower,
Upper
FROM
[Test-1.xlsx]
(ooxml, embedded labels, table is Grade);

IntervalMatch:
IntervalMatch(Score)
Load Distinct Lower, Upper Resident Grade;

Left Join (IntervalMatch)
Load * Resident Grade;

Drop Table Grade;
Drop Fields Lower, Upper;

MK_QSL — Sat, 17 Oct 2020 09:34:30 GMT

If you want only to edit your expression without changing script then you can use below expression.

Max(Aggr(If(sum(Score)>= sum(Lower) and sum(Score) <= sum(Upper),Grade),Student, Subject, Score,Lower,Upper))

only(Aggr(If(sum(Score)>= sum(Lower) and sum(Score) <= sum(Upper),Grade),Student, Subject, Score,Lower,Upper))

Aggr(If(sum(Score)>= sum(Lower) and sum(Score) <= sum(Upper),Grade),Student, Subject, Score,Lower,Upper)

tracycrown — Sat, 17 Oct 2020 15:33:27 GMT

Dear MK_QSL

Thank you so much for your kind and quick response, your methods (Script & Expression) are VERY useful and just in time for me to resolve the issue.

Thank you, Tracy

tracycrown — Sun, 18 Oct 2020 16:00:31 GMT

Dear MK_QSL

Sorry to trouble you again as I have encountered another problem after adding a new field, Term.

Table A - Grade is OK

Table B - Grade is wrong if using average score ?

Table C - Not sure why I cannot use sum(Lower) and sum(Upper) in expression ?

Thank you in advance for your advice again, Tracy