Determining employee raise from previous record

[alecjones-DAVEY]

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. 

EmpName	EmpNo	Salary	Start Date	End Date	Previous Salary	RaiseFlag
AAA	1111	50000	1/1/2019	1/1/2020	0	0
AAA	1111	55000	1/1/2020	2/3/2021	50000	1
AAA	1111	60000	2/3/2021	12/31/9999	55000	1
BBB	2222	40000	1/1/2019	1/2/2020	0	0
BBB	2222	45000	1/2/2020	2/4/2021	40000	1
BBB	2222	45000	2/4/2021	12/31/9999	45000	0

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. 

BBB	2222	45000	2021	2021	45000	0
CCC	3333	30000	2019	2020	45000	0
CCC	3333	35000	2020	2021	30000	1

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

Qlik Cloud 

[danilostochi]

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;