Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources. READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
muniyandi
Creator III
Creator III

How to get LEAD & LAG function in Qlikview

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

9 Replies
muniyandi
Creator III
Creator III
Author

Anybody know the answer ?, It will helpful for me.

Clever_Anjos
Employee
Employee

LAG function returns from previous row, right?

Use function Previous() or Peek().

There´s no QlikView function that returns values from next row

muniyandi
Creator III
Creator III
Author

Hi Anjos,


LEAD FUNCTION are not possible ?

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Above() will work in a chart - use a negative number for the offset parameter.

Clever_Anjos
Employee
Employee

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

jerem1234
Specialist II
Specialist II

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!

muniyandi
Creator III
Creator III
Author

Hi Jason ,


I want some requirement to achieve in script level, any possible to get Lead Function the result.

canariam
Contributor III
Contributor III

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
CITY CITY_SALARY LAG LEAD
New York12220.561234.56
Toronto1234.5612220.5619118.9
Vancouver19118.91234.56
Hamdi_G
Contributor III
Contributor III

Please will the result still correct if there was many values (not just 3 lines of values) ?