Howto: Take previous field value while none is available

[reporting_neu]

Hello together,

I have Part, Year, Month and Price fields.

Like:

Part	Year	Month	Price
10000	2021	12	100
10000	2022	01
10000	2022	02	105
10000	2022	03	104
10000	2022	04
10000	2022	05
10000	2022	06	108
10000	2022	07	111

I want the value from the previous month to be used for the empty fields. And if the following field is also empty, such as here in May 2022, then the value should be written away until a value is available again.

The turn of the year is also important. that this is observed.

I started like this and previously marked the empty months with "Flag":

NoConcatenate
AVG:
Load
  Year
, Month
, Part
, if(Previous(Year) = Year and Previous(Upper(Text(Part))) = Upper(Text(Part)) and Price = 'Flag', Previous(Price), 
    if(Previous(Year) = (Year -1) and Previous(Month) = 12 and Previous(Upper(Text(Part))) = Upper(Text(Part)) and Price= 'Flag', Previous(Price), Price)) as Price
Resident TMP_AVG
Order by Part, Year, Month ASC;

However, I can't get the fields to be filled if the field was empty beforehand. The months are also doubled.

Could anyone help me, please?

[Aurelien_Martinez · Accepted solution]

Hi,

I have try this :

LOAD
  Part,
  Year,
  Month,
  If(Len(Trim(Price))=0 and Peek(Part) = Part and (Peek(Year)=Year or (Peek(Year)=Year-1 and Peek(Month)=12)),
    Peek(Price2),
    Price
  ) as Price2,
  Price
Resident data
Order by 
  Part,
  Year,
  Month asc
;

DROP Table data;

DROP Field Price;
RENAME Field Price2 to Price;

[reporting_neu]

Thank you @Aurelien_Martinez for your support 😀

I took a look at your script and added the months and slightly adjusted it.
But unfortunately the script doesn't work for me.

NoConcatenate
Test:
LOAD
  Part,
  Year,
  Month,
  If(Year= Peek(Year) and (Month-1) = Previous(Month) and Price= 'Flag', Peek('NewPrice'), Price) as NewPrice,
Price
Resident Data
Order by 
  Artikel,
  BelegJahr,
  BelegMonat ASC
;

What is wrong? Can you help me again?

[Aurelien_Martinez]

If(Year= Peek(Year) and (Month-1) = Peek(Month) and Price= 'Flag', Peek(NewPrice), Price) as NewPrice

[reporting_neu]

I added the Price as a dimension to the table. As a result, Qlik was unable to interpret the value. I have now correctly inserted the value as a key figure and then everything works.

Tank you so much! 😄