Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
alecjones-DAVEY
Contributor
Contributor

Determining employee raise from previous record

Hi everyone,

I'm trying to determine if an employee has gotten a raise or not based on their previous salary and flag it. I'm struggling to find a way to pull the salary from the previous record of an employee. Below is an example of what I'm trying to accomplish. There can be some cases where an employee could have two records with the same salary. The logic essentially is if the salary - previous(salary) > 0, then Raise = '1', '0' but I can't seem to get that to work in Qlik. The date 12/31/9999 is a current record. 

EmpNameEmpNoSalaryStart DateEnd DatePrevious SalaryRaiseFlag
AAA1111500001/1/20191/1/202000
AAA1111550001/1/20202/3/2021500001
AAA1111600002/3/202112/31/9999550001
BBB2222400001/1/20191/2/202000
BBB2222450001/2/2020

2/4/2021

400001
BBB2222450002/4/202112/31/9999450000

 

I have tried using the above() function as well, but this function doesn't have the ability to be limited to a certain employee. It will just read the previous record in the table. So in the case above, the first record for Employee CCC will pull the previous record which is for Employee BBB (see below for example). I need to pull the previous records salary for that specific employee. 

BBB22224500020212021450000
CCC33333000020192020450000
CCC33333500020202021300001

 

 If you need any additional information or have any questions, please let me know. Thank you for the help. 

 

Qlik Sense Enterprise SaaS 

1 Reply
danilostochi
Creator II
Creator II

Hi,

try it.

data_set:
LOAD
Date(if([End Date]='12/31/9999', Today(),[End Date] )) as Date_pivot,

* INLINE [
EmpName, EmpNo, Salary, Start Date, End Date
AAA, 1111, 50000, 01/01/2019, 01/01/2020
AAA, 1111, 55000, 01/01/2020, 02/03/2021
AAA, 1111, 60000, 02/03/2021, 12/31/9999
BBB, 2222, 40000, 01/01/2019, 01/02/2020
BBB, 2222, 45000, 01/02/2020, 02/04/2021
BBB, 2222, 45000, 02/04/2021, 12/31/9999
];


data_set2:
LOAD
EmpName,
EmpNo,
Salary,
[Start Date],
Date_pivot,
if(Previous(EmpName)=EmpName and Previous(EmpNo)=EmpNo, Previous(Salary),Salary ) as [Previous Salary],
if(if(Previous(EmpName)=EmpName and Previous(EmpNo)=EmpNo, Previous(Salary),Salary )=Salary,0,1) as RaiseFlg


Resident data_set
order by
EmpNo, EmpNo, Date_pivot asc;

drop Table data_set;

danilostochi_0-1631664712722.png

 

+55(44) 9 9993-3605, WhatsApp
E-Mail or Skype - danilo16stochi@hotmail.com