Fill column value with previous not null value

[arvind_patil]

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

[tresesco]

This post might be helpful here: How to populate sparsely populated field

[PrashantSangle]

You have to perform multiple steps to achieve required result.

Use below logic to get required output

1: Load distinct date from base data table
2: load distinct employee from base table.
3: cross join with o/p of 1 and 2nd step.
4: load o/p of 3rd table then left join it with base table on the key of date & employeeName
5: sort o/p of table 4 by date, employeeName
6: use peek() and if() together to find the missing salary.
7: drop unnecessary table.

Regards,
Prashant Sangle

[arvind_patil]

Dear Prashant,

Thanks for your reply. But  here I required exact logic. I allready know the steps.

Thanks & regards,

Arvind Patil

[arvind_patil]

Dear Tresco,

I have refer same example but I require output with  a column employee .

Thanks & Regards,

Arvind Patil

[sunny_talwar · Accepted solution]

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;

[arvind_patil]

Hi Sunny,

It works.

Thanks & Regards,

Arvind Patil