topic Re: How to get LEAD & LAG function in Qlikview in QlikView

How to get LEAD & LAG function in Qlikview

muniyandi — Thu, 12 Jun 2014 12:40:50 GMT

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

Re: How to get LAG function in Qlikview

muniyandi — Thu, 12 Jun 2014 12:58:16 GMT

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

Re: How to get LAG function in Qlikview

Clever_Anjos — Thu, 12 Jun 2014 12:59:31 GMT

LAG function returns from previous row, right?

Use function Previous() or Peek().

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

Re: How to get LAG function in Qlikview

muniyandi — Thu, 12 Jun 2014 13:07:20 GMT

Hi Anjos,

LEAD FUNCTION are not possible ?

Re: How to get LAG function in Qlikview

Jason_Michaelides — Thu, 12 Jun 2014 13:08:44 GMT

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

Re: How to get LAG function in Qlikview

Clever_Anjos — Thu, 12 Jun 2014 13:15:08 GMT

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

Re: How to get LAG function in Qlikview

jerem1234 — Thu, 12 Jun 2014 13:15:10 GMT

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!

Re: How to get LAG function in Qlikview

muniyandi — Thu, 12 Jun 2014 13:17:47 GMT

Hi Jason ,

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

Re: How to get LEAD & LAG function in Qlikview

canariam — Thu, 17 Jul 2014 06:21:50 GMT

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 York	12220.56		1234.56
Toronto	1234.56	12220.56	19118.9
Vancouver	19118.9	1234.56

Re: How to get LEAD & LAG function in Qlikview

Hamdi_G — Wed, 22 Jul 2020 11:46:34 GMT

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