Hi!
I'm fairly new to Qlik and I've now been stuck on an issue for a bit more than a day, so figured I'd try my luck here.
Essentially I have a table 'Temp_values' which contains an item number, date and average stock on that date, which is grouped by item number and date. I then try to create a Calendar which holds all the dates in the range i've selected (I think it works as intended up to this point but not completely sure).
What I'm having issues with is that there are a lot of null values as far from every item has a transaction associated with it each day, and I want to replace every null value after the first transaction of each item with the previous known value so that it can be plotted nicely and I can take an average per time period that is accurate.
My code looks like this:
Temp_values:
Load
ItemNumber,
Date,
avg(StockLevel) as StockLevel
Resident data_temp
group by ItemNumber, Date;
MinMaxDate:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Temp_values;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate')-1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');
Calendar:
Load Date(RecNo()+$(vMinDate)) as Date AutoGenerate vMaxDate - vMinDate;
Join(Calendar) load distinct ItemNumber resident Temp_values;
Left Join(Calendar) load * resident Temp_values;
values:
NoConcatenate
Load
Date,
ItemNumber,
If(RowNo()<>1,If(IsNull(StockLevel) and Match(ItemNumber, Previous(ItemNumber)), Peek(StockLevel), StockLevel)) as StockLevel
Resident Calendar
Order by ItemNumber, Date;
If additional information would be helpful, please let me know. Thanks.
