How to get LEAD & LAG function in Qlikview

[muniyandi]

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

[muniyandi]

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

[Clever_Anjos]

LAG function returns from previous row, right?

Use function Previous() or Peek().

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

[muniyandi]

Hi Anjos,

LEAD FUNCTION are not possible ?

[Jason_Michaelides]

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

[Clever_Anjos]

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]

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]

Hi Jason ,

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

[canariam]

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

[Hamdi_G]

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