Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

p_gerbaux
Not applicable

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.

1 Solution

Accepted Solutions
tresesco
Not applicable

Re: How find the last record (previous) of a tuple

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;

4 Replies
tresesco
Not applicable

Re: How find the last record (previous) of a tuple

PFA

p_gerbaux
Not applicable

Re: How find the last record (previous) of a tuple

Hi,

I don't understand : PFA ?

tresesco
Not applicable

Re: How find the last record (previous) of a tuple

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;

p_gerbaux
Not applicable

Re: How find the last record (previous) of a tuple

Great!

Thanks