Question on Data Load Script

[#1]

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

[sunny_talwar]

Some of the Appraisal Ratings are missing in your input data, but they show up in output. Is that a typo?

[rupamjyotidas]

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

[#4]

Yes Sorry, that's typo.

[sunny_talwar]

If what rupamjyotidas‌ has provided doesn't work, would you be able to update the sample so that we can get it to work