Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am business user and am new to Qlik Sense. I am trying to build an app for myself and have a situation as below:
I want to evaluate and assign values at the time of running data load script. I have three columns in my source table - Employee ID, Appraisal Rating & Reason (for leaving). I want to evaluate values in two columns and assign value to Employee ID in the 4th column.
Business Rules for Evaluations are as follows:
Rule 1: If Appraisal Rating is 4 then assign "Final Rating" - C4, If Appraisal Rating is 1 or 2 then assign "Final Rating" - C1 and If Appraisal Rating is 3 or U then assign "Final Rating" - C2
Rule 2: If Reason is 'Termination' or 'Others' then assign "Final Rating" C4
I am able to evaluate and assign Final Rating using mapping function but I am able to evaluate it for only one column (Appraisal Rating), I am not able to do it for 2nd column in the same load script.
Can someone suggest how I can evaluate both columns in the same load script? I am putting a small sample of my source table and expected resulting table for your reference.
Source Table
Employee ID | Appraisal Rating | Reason |
1 | 1 | Others |
2 | 2 | Resignation |
3 | 3 | Others |
4 | 4 | Marriage |
5 | Termination | |
6 | 1 | Resignation |
7 | 2 | Termination |
8 | 3 | Termination |
9 | Termination | |
10 | U | Termination |
11 | 1 | Termination |
12 | Termination | |
13 | Termination | |
14 | 4 | Relocation |
15 | U | Termination |
16 | 1 | Termination |
17 | 2 | Termination |
18 | 3 | Termination |
19 | 4 | Termination |
20 | U | Termination |
Expected Results after data load:
Resulting Table:
Employee ID | Appraisal Rating | Reason | Final Rating |
1 | 1 | Others | C4 |
2 | 2 | Resignation | C1 |
3 | 3 | Others | C4 |
4 | 4 | Marriage | C4 |
5 | U | Termination | C4 |
6 | 1 | Resignation | C1 |
7 | 2 | Termination | C4 |
8 | 3 | Termination | C4 |
9 | 4 | Termination | C4 |
10 | U | Termination | C4 |
11 | 1 | Termination | C4 |
12 | 2 | Termination | C4 |
13 | 3 | Termination | C4 |
14 | 4 | Relocation | C4 |
15 | U | Termination | C4 |
16 | 1 | Termination | C4 |
17 | 2 | Termination | C4 |
18 | 3 | Termination | C4 |
19 | 4 | Termination | C4 |
20 | U | Termination | C4 |
Some of the Appraisal Ratings are missing in your input data, but they show up in output. Is that a typo?
This should work
Test:
LOAD [Employee ID],
[Appraisal Rating],
Reason,
if(Match(Reason,'Termination','Others')>0,'C4',if([Appraisal Rating]='U','C2',Pick([Appraisal Rating],'C1','C1','C2','C4'))) as FinalRating
FROM
[https://community.qlik.com/thread/234902]
(html, codepage is 1252, embedded labels, table is @1);
Yes Sorry, that's typo.
If what rupamjyotidas has provided doesn't work, would you be able to update the sample so that we can get it to work