Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All!
I have to make stock levels report, based on mutations.
Qlik needs to fill empty rows with last registered stock level, but when first (first date) is not registered need to be as 0.
I have already this script but is not working.
temp:
LOAD
DIVCODE,
PARTNO,
%REPORT_DATE,
"Week",
"Year",
"Month",
MonthNo,
"Day",
"WeekDay",
Quarter,
YearWeek,
X08BAL
FROM [lib://2_Temp/BALANCE_cal_1.qvd]
(qvd);
Temp1:
load
DIVCODE,
PARTNO,
%REPORT_DATE,
date(if(PARTNO=Peek(PARTNO),Peek(%REPORT_DATE),%REPORT_DATE+1)) as DATE1,
"Week",
"Year",
"Month",
MonthNo,
"Day",
"WeekDay",
Quarter,
YearWeek,
X08BAL
resident temp
order by PARTNO, %REPORT_DATE desc;
final:
Load *,
if(DATE1>%REPORT_DATE,date(%REPORT_DATE+IterNo()-1)) as %REPORT_DATE1
Resident Temp1
While %REPORT_DATE + IterNo() - 1 < DATE1;
STORE final INTO 'LIB://2_Temp/BALANCE_cal_Final.qvd' (qvd);
drop table temp,Temp1;
This is what I need:
Are you trying to fill Dates or Stock Level field? You image shows Stock Level, but you code is seem to be doing it for %Report_Date?
Check this
Table:
LOAD * INLINE [
Date, Product, Stock Level
1-1-2018, A
2-1-2018, A, 1
3-1-2018, A
4-1-2018, A, 2
5-1-2018, A
1-1-2018, B, 5
2-1-2018, B, 4
3-1-2018, B,
4-1-2018, B
5-1-2018, B
1-1-2018, C,
2-1-2018, C, 3
3-1-2018, C,
4-1-2018, C, 5
5-1-2018, C
1-1-2018, D,
2-1-2018, D
3-1-2018, D,
4-1-2018, D, 6
5-1-2018, D
];
FinalTable:
NoConcatenate
LOAD Date,
Product,
If(Product = Previous(Product), If(Len(Trim([Stock Level])) = 0, Peek('Stock Level'), [Stock Level]), 0) as [Stock Level]
Resident Table
Order By Product, Date;
DROP Table Table;
Sorry for late reaction. This solution is not working with my data set. My data is sorted by Date not by product.
Should I sort this data like you have? If so, how to do it? I can't fine any sorting function.
I skipped this part:
Order By Product, Date;
So please ignore my previous post, but still data is not filled.
