Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to fill in missing values using the Peek function. I am using the same logic as found here: http://community.qlik.com/docs/DOC-3786 but the Peek function doesn't seem to be working right.
Here my script, and attached is the data. I've also attached a screenshot of the results I am getting. It works to fill in one '0' value, but if there are multiple '0' values in a row then it doesn't work.
[Monthly Survey]:
LOAD
CustID,
Date,
ProductID
FROM
[..\Peek test 3.xlsx]
(ooxml, embedded labels, table is Sheet1);
PeekTEST:
NoConcatenate LOAD
CustID,
Date,
ProductID,
rowno() as RowNumber,
If((ProductID=0 or isnull(ProductID))
and CustID = peek(CustID),
Peek(ProductID), ProductID)
as ProductIDTEST
Resident [Monthly Survey]
order by 1,2;
drop table [Monthly Survey];
That's because you peek in ProductID, not in ProductIDTEST where you store the result. Try
If((ProductID=0 or isnull(ProductID))
and CustID = peek(CustID),
Peek(ProductIDTEST), ProductID)
as ProductIDTEST
hi;
try peek('productID', -1)
Fabrice
That's because you peek in ProductID, not in ProductIDTEST where you store the result. Try
If((ProductID=0 or isnull(ProductID))
and CustID = peek(CustID),
Peek(ProductIDTEST), ProductID)
as ProductIDTEST
HI
PFA
This does the trick! Is this because Peek works on the output of the load? Thanks very much