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

[#2]

This works well, but I found one bug here, in one case it showed me this:

Employee	Account	Period	DataAccount	Bench	Flag
Pavel Kovalski	International	Sep-14	0.18	0	1
Pavel Kovalski	Telecom	Feb-14	1.00	0
Pavel Kovalski	Telecom	Mar-14	1.00	0
Pavel Kovalski	Telecom	Apr-14	1.00	0
Pavel Kovalski	Telecom	May-14	1.00	0
Pavel Kovalski	Telecom	Jun-14	0.47	0
Pavel Kovalski		Jan-15		1
Pavel Kovalski		Feb-15		1
Pavel Kovalski		Mar-15		1
Pavel Kovalski		Apr-15		1

I want it to put 1 in front of Jun-14

[#3]

Sorry, it's not a bug! This works perfectly great!

Thanks!

[jopmoekotte]

I think Peter already solved it for you, however I personally prefer using keyfields (combining multiple fields) to avoid multiple IF AND statements. (however, temporarily, bloating your table).

my suggestion:

Data_raw:

LOAD *,

  Date(Date#(Period, 'MMM-YY')) as Date,

  Employee &'_'& Account  as Key_Peek

;

LOAD * 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(Key_Peek=Peek(Key_Peek,-1),if(Bench<>Peek(Bench,-1),1,null()),null()) as Flag

RESIDENT Data_raw ORDER BY Key_Peek, Date DESC;

drop table Data_raw;