Coding in Qlik - Retrieve latest value of the previous year if value has not been updated in that year

[Cla10]

Hello everyone, I would like to ask for your help. I have a table with the following fields: Item Code, Date of update, Value, Year.

I have to calculate the latest value of "Value" field for each year;

If  the value is not updated, I must take the latest value of the previous year. I've already calculated the latest value for each year using lastvalue () and group by.

I'm having troubles in the "creation" of the year if the value has not been updated in that year, peeking the latest value of the previous year.

I attach an example:

I've tried different approaches but none of them gave me the result in red.

Thank you for the support! 

[JandreKillianRIC]

Hi @Cla10 

I just added another code (To cater for more than one) This will also ensure that if code one starts at 2012 and ends at 2025 the data will only be built for those that correlate to the code, vs a code 2 that runs from 2021 to 2024. See my table below 

Here is the script 

DataTmp:
Load * Inline [
Code,Date,Value,Year
11902,44201,9.35,2021
11902,44427,10,2021
11902,45641,12,2024
11905,44201,9.35,2022
11905,44427,10,2023
11905,45641,12,2025
];

TmpCal:
Load 
	Min(Year) as MinYear,
    Max(Year) as MaxYear
Resident DataTmp;

Let zMinYear = Peek('MinYear');
Let zMaxYear = Peek('MaxYear');
Let zYears = zMaxYear - zMinYear + 1;

Drop Table TmpCal;

CodeWithYear:
Load 
	Code, 
    Min(Year) as StartYear,
	Max(Year) as EndYear
Resident DataTmp
Group By Code;

AllYears:
Join (CodeWithYear)
Load 
	$(zMinYear) + Recno() - 1 as YearAll
AutoGenerate($(zYears)); 

CleanUpYears:
Join (DataTmp)
Load 
	Code, 
    YearAll as Year
Resident CodeWithYear
Where YearAll >= StartYear and YearAll <= EndYear; 

Drop Table CodeWithYear; 

Data:
NoConcatenate
Load 
	Code, 
    Year, 
    Date, 
    IF(IsNull(Value), Peek('NewValue'), Value) as NewValue
Resident DataTmp
Order By 
	Code, 
    Year;

Drop Table DataTmp; 

Regards Jandre