Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik community,
I have 2 fields, CRSE_GRADE_OFF and SCORE_LETTER.
CRSE_GRADE_OFF include : A+,A,A-,B+,B,B-,C+,C,D+,D,P,F
SCORE_LETTER include : A,B,C,D,F, E,M,P,S,U and X.
I have created a dimension (Performance) in my table:
=if(CRSE_GRADE_OFF<SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0
,'BETTER',
if(CRSE_GRADE_OFF>SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0
,'WORSE',
IF( CRSE_GRADE_OFF=SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0,'EQUAL')))
Results:
As shown, B+ should be 'BETTER' than B, but results show WORSE.
Also if the CRSE_GRADE_OFF = P and SCORE_LETTER = E or M or S or U or X, I want the result to be better.
However in the 2nd screenshot, it shows P as worse than M.
Is there any way I can achieve this?
Thank you so much in advance.
Hi Wong,
You should create two mapping tables in the script, one for SCORE_LETTER and one for CRSE_GRADE_OFF.
I provide examples below, but you need to correct Value in mapping tables
ScoreLetterMap:
Mapping Load * Inline[
Key, Value
A, 11
B, 8
C, 5
D, 3
F, 1
E, 6
M,5
P, 4
S, 3
U, 2
X, 1
];
CrseGradeMap:
Mapping Load * Inline[
Key, Value
A+,12
A,11
A-,10
B+,9
B,8
B-,7
C+,6
C,5
D+,4
D,3
P,2
F,1
];
Then update columns in the script:
Dual(CRSE_GRADE_OFF, ApplyMap('CrseGradeMap', CRSE_GRADE_OFF))As CRSE_GRADE_OFF
Dual(SCORE_LETTER, ApplyMap('ScoreLetterMap', SCORE_LETTER)) As SCORE_LETTER
After this you can create Performance column, and will have correct result because you will compare numbers.
Just correctly change values in mapping tables
Thanks,
Vitalii
You can comparing Text into If..Else condition. So it can not be like that as Qlik can compare in Integer format. Better to maintain Mapping table. Or Can we have the same application. so then we can test.
Can you give a solution using a mapping table as an example?
Hi Wong. I think that is exactly what is specified in the condition
IF(CRSE_GRADE_OFF > SCORE_LETTER, 'WORSE',... If('P' > 'E', 'WORSE''...
What I am looking for is if CRSE_GRADE_OFF = P and SCORE_LETTER = E or M or S or U or X, I want the result to be 'BETTER' and not 'WORSE'.
Well, then change the condition
=if(CRSE_GRADE_OFF < SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0
,'WORSE',
if(CRSE_GRADE_OFF>SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0
,'BETTER',
IF( CRSE_GRADE_OFF=SCORE_LETTER and len(trim(CRSE_GRADE_OFF))>0,'EQUAL')))
This condition will work if CRSE_GRADE_OFF = P
But all the other grades will be wrong.
Eg. if CRSE_GRADE_OFF = 'A', and SCORE_LETTER = 'B', this condition will show that 'WORSE' when the desired result is 'BETTER'.
I hope this helps in your understanding of the problem.
Of course, because A < B. It's counted by alphabetic order
Hi Wong,
You should create two mapping tables in the script, one for SCORE_LETTER and one for CRSE_GRADE_OFF.
I provide examples below, but you need to correct Value in mapping tables
ScoreLetterMap:
Mapping Load * Inline[
Key, Value
A, 11
B, 8
C, 5
D, 3
F, 1
E, 6
M,5
P, 4
S, 3
U, 2
X, 1
];
CrseGradeMap:
Mapping Load * Inline[
Key, Value
A+,12
A,11
A-,10
B+,9
B,8
B-,7
C+,6
C,5
D+,4
D,3
P,2
F,1
];
Then update columns in the script:
Dual(CRSE_GRADE_OFF, ApplyMap('CrseGradeMap', CRSE_GRADE_OFF))As CRSE_GRADE_OFF
Dual(SCORE_LETTER, ApplyMap('ScoreLetterMap', SCORE_LETTER)) As SCORE_LETTER
After this you can create Performance column, and will have correct result because you will compare numbers.
Just correctly change values in mapping tables
Thanks,
Vitalii