How find the last record (previous) of a tuple

[Anonymous]

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 :

recordnumber	datearrival	timearrival	name	site	reasonofvisit	Lastvisit
1	1/01/2013	07:03	Fred	Agency	Visit	-
2	1/01/2013	10:02	Alan	Agency	visit	-
3	1/05/2013	07:05	Fred	Agency	Revisit	1
4	1/05/2013	12:59	Fred	Agency	Revisit	3
5	5/05/2013	08:00	Mandy	bank	visit	-

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.

[tresesco]

PFA

[Anonymous]

Hi,

I don't understand : PFA ?

[tresesco · Accepted solution]

PFA - please find attachment

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

];

Load
*,
If(Peek('name')=name,Peek('recordnumber')) as Lastvisit
Resident Data Order By name, recordnumber;

Drop table Data;

[Anonymous]

Great!

Thanks