Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have an inline table named "Emp" below need to calculate increased salary For years based on Salary hike given.
Year, EMPID, Current Salary, Hike
'2001', '1', '20000', '20%'
'2001', '2', '30000', '30%'
'2001', '3', '40000', '40%'
'2003', '1', '50000', '20%'
'2003', '3', '70000', '40%'
'2004', '2', '65000', '30%'
'2005', '3', '76000', '50%'
Can you please guide me with , how to find out the 1st and last salary based on the employees in Qlik script editor and formula in UI ?
Thanks & Regards,
Ranjana
Hey,
Something like that:
UI:
Dimension EMPID.
firstsortedvalue(CurrentSalary,-Year) and firstsortedvalue(CurrentSalary,Year)
Script:
LOAD
EMPID,
Year,
min(Year) as FirstSalaryYear,
max((Year) as LastSalaryYear
RESIDENT table GROUP BY EMPID;
LEFT JOIN
EMPID,
Year as FirstSalaryYear
Salary as FirstSalary
RESIDENT table;
LEFT JOIN
EMPID,
Year as LastSalaryYear
Salary as LastSalary
RESIDENT table;
First under your inline table load script
// Calculate Increased Salary
Emp_Salary:
LOAD
Year,
EMPID,
Current_Salary,
Hike,
ApplyMap('HikeMap', Hike, null()) as HikeMultiplier,
Current_Salary * (1 + HikeMultiplier) as Increased_Salary
Resident Emp;
// Map Hike Percentage to Multiplier
HikeMap:
Mapping
LOAD
Hike,
Num(Sum(Num#(Hike, '#%')) / 100, '0.00') as HikeMultiplier
Resident Emp
Group By Hike;
// Find 1st and Last Salary for Each Employee
Emp_FirstLastSalary:
LOAD
EMPID,
FirstSortedValue(Increased_Salary, Year) as First_Salary,
FirstSortedValue(Increased_Salary, -Year) as Last_Salary
Resident Emp_Salary
Group By EMPID;
DROP Table Emp, Emp_Salary, HikeMap;
UI Formula
First Salary:
FirstSortedValue(Increased_Salary, Year)
Last Salary:
FirstSortedValue(Increased_Salary, -Year)
Replace "Increased_Salary" with the actual field name you are using in your data model.