Calculating Salary - find out the 1st and last salary in Qlik script editor and formula in UI

[Ranjanac]

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

[Ranjanac]

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

[mruehl]

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;

[Aasir]

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.