2 Replies Latest reply: Oct 1, 2016 7:52 AM by Massimo Grossi RSS

    Keep value through rows

    Ridwaan Shaik

      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.

       

      ex.png

      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

        • Re: Keep value through rows
          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

          • Re: Keep value through rows
            Massimo Grossi

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

            1.png

            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;