Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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.
Thanks,
Muni
Anybody know the answer ?, It will helpful for me.
LAG function returns from previous row, right?
Use function Previous() or Peek().
There´s no QlikView function that returns values from next row
Hi Anjos,
LEAD FUNCTION are not possible ?
Above() will work in a chart - use a negative number for the offset parameter.
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:
Table1:
Load *,
previous(CITY_SALARY) as Lag,
rowno() as ROW_NO
Resident Data;
Temp:
NOCONCATENATE LOAD *,
previous(CITY_SALARY) as Lead
Resident Table1
Order By ROW_NO DESC;
Drop Field ROW_NO;
Hope this helps!
Hi Jason ,
I want some requirement to achieve in script level, any possible to get Lead Function the result.
You can use this.
Enjoy it.
YOURDATA:
LOAD * INLINE
[
CITY, CITY_SALARY
New York, 12220.56
Toronto, 1234.56
Vancouver, 19118.9
];
TEST1:
LOAD *,RowNo() as RN Resident YOURDATA;
TEST2:
LOAD
CITY,
CITY_SALARY,
Peek(CITY_SALARY) as LEAD
Resident TEST1
Order By RN DESC;
TEST3:
LOAD
CITY,
CITY_SALARY,
Peek(CITY_SALARY) as LAG
Resident TEST1
Order By RN ASC;
NoConcatenate
Result:
LOAD CITY,CITY_SALARY,LEAD Resident TEST2;
Inner Join
LOAD CITY,LAG Resident TEST3;
DROP Tables YOURDATA,TEST1,TEST2,TEST3;
Result Table | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Please will the result still correct if there was many values (not just 3 lines of values) ?