If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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?
Accepted Solutions
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;
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;
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?
If(Year= Peek(Year) and (Month-1) = Peek(Month) and Price= 'Flag', Peek(NewPrice), Price) as NewPrice
