4 Replies Latest reply: May 21, 2014 5:38 AM by Philippe Gerbaux RSS

    How find the last record (previous) of a tuple

      Hi Everybody,

       

       

      I don't find the solution for my problem.

      I have one table such below

       

      Data:

      LOAD recordnumber,datearrival,timearrival,name,site,reasonofvisit INLINE [  

          recordnumber,datearrival,timearrival,name,site,reasonofvisit

          1,01/01/2013,07:03,Fred,Agency,visit

          2,01/01/2013,10:02,Alan,Agency,visit

          3,01/05/2013,07:05,Fred,Agency,second_visit

          4,01/05/2013,12:59,Fred,Agency,third-visit

          5,05/05/2013,08:00,Mandy,bank,visit

      ];

       

      I would like to have a table result as below :

       

      recordnumberdatearrivaltimearrivalnamesitereasonofvisitLastvisit
      11/01/201307:03FredAgencyVisit-
      21/01/201310:02AlanAgencyvisit-
      31/05/201307:05FredAgencyRevisit1
      41/05/201312:59FredAgencyRevisit3
      55/05/201308:00Mandybankvisit-

       

      So, the rules is the next one:

      If one person come for a visit, then no lastvisit must be encoded.

      If one person must come back to the same site (revisit) then the recordnumber of the last visit of Fred must be found.

       

      I think that using the function peek is the solution but i don't can it..

       

      Thanks.