Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NewToQlik
Creator
Creator

Comparison of string values

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:
test.PNG

testt2.PNG

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.

1 Solution

Accepted Solutions
vitaliichupryna
Creator III
Creator III

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

View solution in original post

8 Replies
Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
NewToQlik
Creator
Creator
Author

Can you give a solution using a mapping table as an example?

andrey_krylov
Specialist
Specialist

Hi Wong. I think that is exactly what is specified in the condition

IF(CRSE_GRADE_OFF > SCORE_LETTER, 'WORSE',... If('P' > 'E', 'WORSE''...

NewToQlik
Creator
Creator
Author

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'.

andrey_krylov
Specialist
Specialist

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')))

NewToQlik
Creator
Creator
Author

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.

andrey_krylov
Specialist
Specialist

Of course, because A < B. It's counted by alphabetic order

vitaliichupryna
Creator III
Creator III

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