Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;