Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Peek function doesnt work properly

Hi!

I have a table with Employees, Account , DataAccount,Bench

This is a part of my table:

                                                                                                                       

EmployeeAccountPeriodDataAccountBench
Mark SpencerTelecomFeb-151.000
Mark SpencerTelecomMar-151.000
Mark SpencerTelecomApr-151.000
Mark SpencerTelecomMay-151.000
Mark SpencerTelecomJun-151.000
Mark SpencerTelecomJul-151.001
Mark SpencerTelecomAug-151.001
Mark SpencerTelecomSep-151.001
Mark SpencerTelecomOct-151.001
Mark SpencerTelecomNov-151.001
Mark SpencerTelecomDec-151.001

I need to find the last row before the values in Bench appear like this:

EmployeeAccountPeriodDataAccountBenchFlag
Mark SpencerTelecomFeb-151.000
Mark SpencerTelecomMar-151.000
Mark SpencerTelecomApr-151.000
Mark SpencerTelecomMay-151.000
Mark SpencerTelecomJun-151.0001
Mark SpencerTelecomJul-151.001
Mark SpencerTelecomAug-151.001
Mark SpencerTelecomSep-151.001
Mark SpencerTelecomOct-151.001
Mark SpencerTelecomNov-151.001
Mark SpencerTelecomDec-151.001
1 Solution

Accepted Solutions
prieper
Master II
Master II

This should work:

Data_raw:

  LOAD *, DATE(DATE#(Period, 'MMM-YY')) AS Date INLINE [

     Employee, Account, Period, DataAccount, Bench

     Mark Spencer, Telecom, Feb-15, 1.00, 0

     Mark Spencer, Telecom, Mar-15, 1.00, 0

     Mark Spencer, Telecom, Apr-15, 1.00, 0

     Mark Spencer, Telecom, May-15, 1.00, 0

     Mark Spencer, Telecom, Jun-15, 1.00, 0

     Mark Spencer, Telecom, Jul-15, 1.00, 1

     Mark Spencer, Telecom, Aug-15, 1.00, 1

     Mark Spencer, Telecom, Sep-15, 1.00, 1

     Mark Spencer, Telecom, Oct-15, 1.00, 1

     Mark Spencer, Telecom, Nov-15, 1.00, 1

     Mark Spencer, Telecom, Dec-15, 1.00, 1

  ];

Data:

  LOAD

  *,

  IF(Employee = PEEK(Employee) AND Bench <> PEEK(Bench), 1) AS Flag

  RESIDENT

  Data_raw

  ORDER BY

  Employee ASC,

  Date DESC;

DROP TABLE Data_raw;

HTH Peter

View solution in original post

12 Replies
ramoncova06
Specialist III
Specialist III

this has been working for me

if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(Account),

     if( Previous(Bench)= Bench, peek('Flag') 1),0) as Flag

Not applicable
Author

Nope,

it shows me an Error.

peek ('Flag') expression isn't working

jolivares
Specialist
Specialist

In this case you have to explore the next record to see if the bench has change, but this is no possible.  In this case i do the following:

T1:

Load.... " your table"

Then...

Join (T1)

Load Employee, Account, Max(Period) as Period, DataAccount, Bench, 1 as Flag

Resident T1

Where Bench = 0

Group By Employee, Account, DataAccount, Bench;

Check it out...

Not applicable
Author

no, it doesnt give me the desired result. I want it to show me exactly like that:

EmployeeAccountPeriodDataAccountBenchFlag
Mark SpencerTelecomFeb-151.000
Mark SpencerTelecomMar-151.000
Mark SpencerTelecomApr-151.000
Mark SpencerTelecomMay-151.000
Mark SpencerTelecomJun-151.0001
Mark SpencerTelecomJul-151.001
Mark SpencerTelecomAug-151.001
Mark SpencerTelecomSep-151.001
Mark SpencerTelecomOct-151.001
Mark SpencerTelecomNov-151.001
Mark SpencerTelecomDec-151.001
ramoncova06
Specialist III
Specialist III

was missing a comma

temp:

LOAD Employee,

     Account,

     Period,

     DataAccount,

     Bench

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

temp:

load

  Employee,

     Account,

     Period,

     DataAccount,

     Bench,

     if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(DataAccount),

     if(Previous(Bench)= Bench, peek('Flag'), 1),0) as Flag

resident temp

order by Employee,Period,Account ;

drop table temp;

ramoncova06
Specialist III
Specialist III

if you only want it to show for when the change happen switch it to this

if(Employee = Previous(Employee) and Account = Previous(Account) and DataAccount = Previous(DataAccount),

     if(Previous(Bench)= Bench, null(), 1)) as Flag

Not applicable
Author

I want to mark the row just before the change happens

prieper
Master II
Master II

This should work:

Data_raw:

  LOAD *, DATE(DATE#(Period, 'MMM-YY')) AS Date INLINE [

     Employee, Account, Period, DataAccount, Bench

     Mark Spencer, Telecom, Feb-15, 1.00, 0

     Mark Spencer, Telecom, Mar-15, 1.00, 0

     Mark Spencer, Telecom, Apr-15, 1.00, 0

     Mark Spencer, Telecom, May-15, 1.00, 0

     Mark Spencer, Telecom, Jun-15, 1.00, 0

     Mark Spencer, Telecom, Jul-15, 1.00, 1

     Mark Spencer, Telecom, Aug-15, 1.00, 1

     Mark Spencer, Telecom, Sep-15, 1.00, 1

     Mark Spencer, Telecom, Oct-15, 1.00, 1

     Mark Spencer, Telecom, Nov-15, 1.00, 1

     Mark Spencer, Telecom, Dec-15, 1.00, 1

  ];

Data:

  LOAD

  *,

  IF(Employee = PEEK(Employee) AND Bench <> PEEK(Bench), 1) AS Flag

  RESIDENT

  Data_raw

  ORDER BY

  Employee ASC,

  Date DESC;

DROP TABLE Data_raw;

HTH Peter

jolivares
Specialist
Specialist

I think you can use the last solution if you do this:

...

Order by Employee, Acouunt, Bench desc, Period desc;