Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Peek and Previous

Hi All,

I am using Peek and Previous function on the given table

 

EmpIDSalary
10020000
30024000
20021000
50017000
70019000
40028000
60023000
80030000
100034000
90032000

I am using the code

PRE:

LOAD

    EmpID,

    Salary

FROM [lib://F/qliksense\Peek and Previous.xlsx]

(ooxml, embedded labels, table is Sheet1);

//  Load*,

//  max(Salary) as msal Group By EmpID;

// Peek(msal,0,'PSI')as Abc;

PSI:

Load

EmpID,

    Salary,

    //max(Salary) as msal ,

    Previous(Salary) as Previoussalary

    Resident PRE  Order By EmpID,Salary;

    Drop Table PRE;

     

    Exit Script;

This code gives me the Previous salary in result but i am not able to calculate the max salary and how to i get this max salary on my result by using Peek.

I need result just like mentioned below

   

EmpIDSalaryPrevious salaryPeeksal
10020000-34000
300240002000034000
200210002400034000
500170002100034000
700190001700034000
400280001900034000
600230002800034000
800300002300034000
1000340003000034000
900320003400034000
Sumit Kumar Srivastava
2 Replies
Highlighted
Master II
Master II

Re: Peek and Previous

maybe like this:

1:

LOAD * INLINE [

    EmpID, Salary

    100, 20000

    300, 24000

    200, 21000

    500, 17000

    700, 19000

    400, 28000

    600, 23000

    800, 30000

    1000, 34000

    900, 32000

];

2:

LOAD EmpID,

     Salary,

     Previous(Salary) as PreviousSalary  

Resident 1; DROP table 1;

left join

Load

  max(Salary) as Peeksal

Resident 2 ;

Highlighted
Specialist III
Specialist III

Re: Peek and Previous

PSI:

Load

EmpID,

Salary,

    //max(Salary) as msal ,

    Previous(Salary) as Previoussalary,

if(Salary > Peek('MaxSalary', -1) or IsNull(Peek('MaxSalary', -1)) , Salary, Peek('MaxSalary', -1)) as MaxSalary

Resident PRE  Order By EmpID,Salary;

Drop Table PRE;

Actually, scratch that - a join of max(Salary) would probably be better