Get Latest Salary in place of Null considering Last Latest Date

[harshal98]

Want the last latest salary in place of Null...Considering the last latest date before 2020

InputOutput

[Qiu]

@harshal98 
Like this?

[echuong1]

This is easily accomplished with a multi-row formula. I'd start off by sorting my records based on the date. I also sorted by the ID, so my records would be in "groups" based on the ID and was easier to look at. From there, I used a multi-row formula to check to see if the salary was missing. If it was, I took it from the row above. My multi-row was grouped by the ID field, effectively processing the ID groups separately.

With the way your data was set up, the missing salaries were coming in as "[Null]" instead of an actual null value. I used a formula at the beginning to clean this up and change them to actual null values. This text also made the column's datatype a string, so I used a Select at the very end to change it to a numeric type.

Hope this helps!