Keep value through rows

[Anonymous]

Hi,

I'm trying to find a way to keep a value through multiple rows of information in script. I tried firstvalue & lastvalue, but it doesnt help because i potentially have duplicates (that should be there). I have a scenario where a person works on a reference, and can choose to close and work on this item about 2 hours later, however someone else can also work on this item as well. I need to keep the first time he worked on this item and stopped, and thereafter start a new calculation when he works on it again.

I need to carry the start value till the end as per column 'start' in the example above? Note person B can start and end the reference twice in a day, but there will be a other references in between (so he wont start the item a second time, without references in between). I believe peek could work, but im struggling to keep a value through all rows as the number of rows vary...

Would really appreciate the assistance on how to achieve this in script..

thanks

[marcus_sommer]

You logic isn't quite clear to me but it sounds that you need to check the previous records which could be done in a sorted load with Peek() or Previous() ?

- Marcus

[maxgro · Accepted solution]

I think this should works; the condition in bold check same reference, same person, not more thsan 2 hours

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

Z:

load * inline [

reference, create_datetime, datetime, person

1, 16/09/2016 10:53:48, 22/09/2016 08:18:40,A

1, 16/09/2016 10:53:48, 22/09/2016 08:18:41,A

1, 16/09/2016 10:53:48, 22/09/2016 08:18:50,A

1, 16/09/2016 10:53:48, 22/09/2016 08:50:05,A

1, 16/09/2016 10:53:48, 22/09/2016 08:53:29,A

1, 16/09/2016 10:53:48, 22/09/2016 08:53:40,A

1, 16/09/2016 10:53:48, 20/09/2016 08:21:38,B

1, 16/09/2016 10:53:48, 20/09/2016 08:21:38,B

1, 16/09/2016 10:53:48, 20/09/2016 08:23:18,B

1, 16/09/2016 10:53:48, 20/09/2016 08:24:38,B

1, 16/09/2016 10:53:48, 20/09/2016 08:24:38,B

1, 16/09/2016 10:53:48, 20/09/2016 08:24:41,B

1, 16/09/2016 10:53:48, 20/09/2016 10:31:53,B

1, 16/09/2016 10:53:48, 20/09/2016 10:31:53,B

1, 16/09/2016 10:53:48, 20/09/2016 10:31:54,B

1, 16/09/2016 10:53:48, 20/09/2016 10:32:06,B

1, 16/09/2016 10:53:48, 20/09/2016 10:33:43,B

1, 16/09/2016 10:53:48, 20/09/2016 10:33:43,B

1, 16/09/2016 10:53:48, 20/09/2016 10:33:48,B

];

Z1:

NoConcatenate load

  *,

  if(reference=peek('reference') and person=Peek('person') and (datetime - Peek('datetime')) <= 2/24 , peek('start'), datetime)  as start

Resident Z

order by reference, person, datetime;

DROP Table Z;