Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Sorry, it's not a bug! This works perfectly great!
Thanks!
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;