Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I have a below table i want update salary of each employee basis on month wise also where salary is null assign previous month or latest available of that month.
Raw data is as below :
EMP:
Load * Inline
[EMPloyee, DATE1 , Salary
Ram, 2018-4-1,1000
Sham,2018-4-1,1000
Ganesh,2018-4-1,1000
Lakhan,2018-4-1,1000
Ram,2018-5-1,1500
Sham,2018-5-1,1500
Ram,2018-6-1,2000
Sham,2018-6-1,2000
Lakhan,2018-7-1,2000
Lakhan,2018-9-1,2500
Ram,2018-9-1,3000
Lakhan,2018-9-1,3000
];
NoConcatenate
EmployeeHistory:
Load EMPloyee,Date(DATE1) as Date,Salary Resident EMP;
Drop Table EMP;
MinMaxDate:
Load Min (Date) as MinDate , Max (Date) as MaxDate resident EmployeeHistory;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate')-1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');
Join (EmployeeHistory)
Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
NoConcatenate
Employee:
Load Date, EMPloyee,
If(IsNull(Salary), Peek(Salary),Salary) as FinalSalary
Resident EmployeeHistory
order by Date; /* so that above values can be propagated downwards */
Drop Table MinMaxDate, EmployeeHistory;
Exit Script;
O/p:
If I Select Ram:
Employee Date Salary
Ram 1-4-2018 1000
Ram 1-5-2018 1500
Ram 1-6-2018 2000
Ram 1-7-2018 2000
Ram 1-8-2018 2000
Ram 1-9-2018 3000
Please help me get this
May be try something like this
EMP: LOAD * INLINE [ EMPloyee, DATE1, Salary Ram, 2018-4-1, 1000 Sham, 2018-4-1, 1000 Ganesh, 2018-4-1, 1000 Lakhan, 2018-4-1, 1000 Ram, 2018-5-1, 1500 Sham, 2018-5-1, 1500 Ram, 2018-6-1, 2000 Sham, 2018-6-1, 2000 Lakhan, 2018-7-1, 2000 Lakhan, 2018-9-1, 2500 Ram, 2018-9-1, 3000 Lakhan, 2018-9-1, 3000 ]; NoConcatenate EmployeeHistory: LOAD EMPloyee, Date(DATE1) as Date, Salary, EMPloyee&Date(DATE1) as Emp_Date_Key Resident EMP; DROP Table EMP; MinMaxDate: Load Min(Date) as MinDate, Max(Date) as MaxDate Resident EmployeeHistory; LET vMinDate = Peek('MinDate',-1,'MinMaxDate')-1; LET vMaxDate = Peek('MaxDate',-1,'MinMaxDate'); Temp: LOAD Date(MonthStart($(vMinDate), IterNo()-1)) as Date Autogenerate 1 While MonthStart($(vMinDate), IterNo()-1) <= $(vMaxDate); Left Join (Temp) LOAD Distinct EMPloyee Resident EmployeeHistory; Concatenate (EmployeeHistory) LOAD Date, EMPloyee Resident Temp Where not Exists(Emp_Date_Key, EMPloyee&Date(Date)); DROP Table Temp; NoConcatenate Employee: LOAD Date, EMPloyee, If(EMPloyee = Previous(EMPloyee), If(IsNull(Salary), Peek('FinalSalary'), Salary), Salary) as FinalSalary Resident EmployeeHistory Order By EMPloyee, Date; /* so that above values can be propagated downwards */ DROP Tables MinMaxDate, EmployeeHistory;
This post might be helpful here: How to populate sparsely populated field
Dear Prashant,
Thanks for your reply. But here I required exact logic. I allready know the steps.
Thanks & regards,
Arvind Patil
Dear Tresco,
I have refer same example but I require output with a column employee .
Thanks & Regards,
Arvind Patil
May be try something like this
EMP: LOAD * INLINE [ EMPloyee, DATE1, Salary Ram, 2018-4-1, 1000 Sham, 2018-4-1, 1000 Ganesh, 2018-4-1, 1000 Lakhan, 2018-4-1, 1000 Ram, 2018-5-1, 1500 Sham, 2018-5-1, 1500 Ram, 2018-6-1, 2000 Sham, 2018-6-1, 2000 Lakhan, 2018-7-1, 2000 Lakhan, 2018-9-1, 2500 Ram, 2018-9-1, 3000 Lakhan, 2018-9-1, 3000 ]; NoConcatenate EmployeeHistory: LOAD EMPloyee, Date(DATE1) as Date, Salary, EMPloyee&Date(DATE1) as Emp_Date_Key Resident EMP; DROP Table EMP; MinMaxDate: Load Min(Date) as MinDate, Max(Date) as MaxDate Resident EmployeeHistory; LET vMinDate = Peek('MinDate',-1,'MinMaxDate')-1; LET vMaxDate = Peek('MaxDate',-1,'MinMaxDate'); Temp: LOAD Date(MonthStart($(vMinDate), IterNo()-1)) as Date Autogenerate 1 While MonthStart($(vMinDate), IterNo()-1) <= $(vMaxDate); Left Join (Temp) LOAD Distinct EMPloyee Resident EmployeeHistory; Concatenate (EmployeeHistory) LOAD Date, EMPloyee Resident Temp Where not Exists(Emp_Date_Key, EMPloyee&Date(Date)); DROP Table Temp; NoConcatenate Employee: LOAD Date, EMPloyee, If(EMPloyee = Previous(EMPloyee), If(IsNull(Salary), Peek('FinalSalary'), Salary), Salary) as FinalSalary Resident EmployeeHistory Order By EMPloyee, Date; /* so that above values can be propagated downwards */ DROP Tables MinMaxDate, EmployeeHistory;
Hi Sunny,
It works.
Thanks & Regards,
Arvind Patil