Peek function doesnt work properly

[#1]

Hi!

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

This is a part of my table:

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

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

Employee	Account	Period	DataAccount	Bench	Flag
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	1
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

[ramoncova06]

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

[#3]

Nope,

it shows me an Error.

peek ('Flag') expression isn't working

[jolivares]

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...

[#5]

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

Employee	Account	Period	DataAccount	Bench	Flag
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	1
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

[ramoncova06]

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]

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

[#8]

I want to mark the row just before the change happens

[prieper · Accepted solution]

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]

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

...

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