Peek and Previous

[srishsum2017]

Hi All,

I am using Peek and Previous function on the given table

EmpID	Salary
100	20000
300	24000
200	21000
500	17000
700	19000
400	28000
600	23000
800	30000
1000	34000
900	32000

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

EmpID	Salary	Previous salary	Peeksal
100	20000	-	34000
300	24000	20000	34000
200	21000	24000	34000
500	17000	21000	34000
700	19000	17000	34000
400	28000	19000	34000
600	23000	28000	34000
800	30000	23000	34000
1000	34000	30000	34000
900	32000	34000	34000

[Frank_Hartmann]

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 ;

[marcus_malinow]

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