Discussion Board for collaboration related to QlikView App Development.
lead , lag function in oracle. I want to know same function action available in qlikview ?
CITY CITY_SALARY LAG FUNCTION LEAD FUNCTION
---------- ----------- -------------------- ----------------
New York 12220.56 1234.56
Toronto 1234.56 12220.56 19118.9
Vancouver 19118.9 1234.56
My Knowledge Lead function are possible using Peek(CITY_SALARY).
How to Get LAG Function.
Not a script level.
At graph time, you can use BELOW function.
An workaround is load the table twice.
One normal and another time using ORDER BY a clause that reverses the table, so PEEK can read the value.
I think this approach is silly. Maybe you can calculate what you want at graph time
What you could do since there is no equivalent function is to reverse the order and use peek (or previous) function again. How you want to do that is up to you. You can use rowno() to get a field to sort by, or use another field you are already using. This would require some extra script, but gets around it. For example something like:
previous(CITY_SALARY) as Lag,
rowno() as ROW_NO
NOCONCATENATE LOAD *,
previous(CITY_SALARY) as Lead
Order By ROW_NO DESC;
Drop Field ROW_NO;
Hope this helps!
You can use this.
LOAD * INLINE
New York, 12220.56
LOAD *,RowNo() as RN Resident YOURDATA;
Peek(CITY_SALARY) as LEAD
Order By RN DESC;
Peek(CITY_SALARY) as LAG
Order By RN ASC;
LOAD CITY,CITY_SALARY,LEAD Resident TEST2;
LOAD CITY,LAG Resident TEST3;
DROP Tables YOURDATA,TEST1,TEST2,TEST3;