Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have fallowing problem.
I need to fill missing data in the gaps, please check picture:
In my case before 1-2-2018 stock level needs to be 0 and after 1-2-2018 must be 19 until another stock level change.
I got this script, but it look that peek() function is not fetching 19 from 1-2-2018.
Stock:
LOAD
SHORTL62,
%REPORT_DATE,
X08BAL
FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]
(qvd);
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek(X08BAL),X08BAL),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
drop table Stock;
Could you advise what needs to be done?
Ok, you already have all dates and you only want to pass values... in that case your original script is failing because peek should use the field created in the same table, that is:
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek([Stock level]),X08BAL),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
drop table Stock;
Peek function needs to have the field name in single quotation marks:
Peek('X08BAL')
Thank you for answer!
I changed peek function as you mentioned, but is the same result.
Stock:
LOAD
SHORTL62,
%REPORT_DATE,
X08BAL
FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]
(qvd);
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL'),X08BAL),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
drop table Stock;
Yes - the quotation marks can be used but are not necessary actually....
I think your problem is that you have to tell which table you are peeking from when you don't include the field in the last table:
Peek( X08BAL , RecNo()-1, 'Stock' )
I still have the gaps.
My Script:
Stock:
LOAD
SHORTL62,
%REPORT_DATE,
X08BAL
FROM [lib://2_Temp/BALANCE_TEMP_2.qvd]
(qvd);
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek('X08BAL',RecNo()-1,'Stock'),X08BAL),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
drop table Stock;
STORE final INTO 'LIB://2_Temp//BALANCE_Final.qvd' (qvd);
Well ... this should work:
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0 or IsNull(X08BAL),peek('Stock level'),Peek(X08BAL,RecNo()-1,'Stock')),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
Hi, in two steps, one to add the end date of the range (in this sample I used today()), and another step to create the extra records, value is only filled for first record but can be easily passed to other records.
tmpLastDate:
LOAD
SHORTL62,
%REPORT_DATE,
If(SHORTL62=Peek(SHORTL62), Peek(%REPORT_DATE), Date(Today())) as lastDate, // fill from the last record until today
[Stock level]
resident tmpData
Order by SHORTL62, %REPORT_DATE desc
;
filledData:
NoConcatenate LOAD
SHORTL62,
%REPORT_DATE+IterNo()-1 as %REPORT_DATE,
If(IterNo()=1, [Stock level]) as [Stock level]
Resident tmpLastDate
While %REPORT_DATE + IterNo()-1 < lastDate;
DROP Table tmpData;
DROP Table tmpLastDate;
Thank you both for help.
The data is still missing. I have no idea what might be the problem.
I attached qvd with selected two products, maybe you can find error there.
Thanks!
Ok, you already have all dates and you only want to pass values... in that case your original script is failing because peek should use the field created in the same table, that is:
final:
NoConcatenate
load
SHORTL62,
%REPORT_DATE,
if(SHORTL62=Previous(SHORTL62),if(len(trim(X08BAL))=0,peek([Stock level]),X08BAL),0) as [Stock level]
resident Stock
order by SHORTL62,%REPORT_DATE;
drop table Stock;
Thank you!
This is correct solution.