Discussion Board for collaboration on QlikView Scripting.
In my model i have such table:
from Sales.qvd (qvd);
For some YearMonths Sum is null.
I need for every EAN, aggregated by Contractor and Chain, where Sum is null, set value from nearest YearMonth.
If there no any nearest YearMonth with sum >0,aggregated by Chain and Contractor, value must be null
Very glad for your help and support.
Go to Solution.
From Sales.qvd (qvd);
If(EAN =Peek('EAN'), If(Len(Trim(Sum)) = 0, Peek('Sum1'), Sum), Sum) as Sum1
Order By Chain, Contractor, EAN, YearMonth;
DROP Table Temp;
AutoNumber(Chain&Contractor&EAN) as Key,
If(Key = Previous(Key) and IsNull(Sum), Peek('NewSum'), Sum) as NewSum
Order By Key, YearMonth;
Drop Table Temp;
From Sales.qvd (qvd)
if(IsNull(Sum) and Previous(EAN)=EAN,Peek(Sum_Final),Sum) as Sum_Final
Resident Temp Order by Chain,Contractor,EAN,YearMonth;