Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Question on Data Load Script

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 IDAppraisal RatingReason
11Others
22Resignation
33Others
44Marriage
5Termination
61Resignation
72Termination
83Termination
9Termination
10UTermination
111Termination
12Termination
13Termination
144Relocation
15UTermination
161Termination
172Termination
183Termination
194Termination
20UTermination

Expected Results after data load:

Resulting Table:  

Employee IDAppraisal RatingReasonFinal Rating
11OthersC4
22ResignationC1
33OthersC4
44MarriageC4
5UTerminationC4
61ResignationC1
72TerminationC4
83TerminationC4
94TerminationC4
10UTerminationC4
111TerminationC4
122TerminationC4
133TerminationC4
144RelocationC4
15UTerminationC4
161TerminationC4
172TerminationC4
183TerminationC4
194TerminationC4
20UTerminationC4
4 Replies

Re: Question on Data Load Script

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

rupamjyotidas
Valued Contributor

Re: Question on Data Load Script

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

Not applicable

Re: Question on Data Load Script

Yes Sorry, that's typo.

Re: Question on Data Load Script

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