Xue Bin,
often the postings are answered pretty fast indeed. But since almost all members of this community are volunteers, there is no guarantee to get an answer within a day or two or even get one at all.
If you don't get an answer, it is probably because people don't fully understand your issue or it is too hard for them to work it out. So I would recommend adding more details (as you are waiting anyway ;-), and making it easier for people to work on your problem by creating e.g. a sample QlikView file.
Back to your problem:
I think you need to match your two tables by time and by UserId, where the time matching has to be matching a point in time (VisitTime) with an interval (EnterTime and ... well, I think up to the next EnterTime?)
Here I might miss some details or requirements, as you are reusing UserID, which probably adds some extra test caes we need to look at.
But I think you can start with adding the ExitTime to your login table and then intervalmatch both tables:
tmpUsers:
LOAD * INLINE [
UserName, UserId, EnterTime
a, 1, 9:00
b, 1, 9:10
];
UserLogins:
LOAD UserName, UserId, EnterTime, time(if(peek(EnterTime),peek(EnterTime), 1)-interval#('01','mm'),'hh:mm') as ExitTime Resident tmpUsers order by EnterTime desc;
drop table tmpUsers;
Rooms:
LOAD * INLINE [
UserId, VisitTime, Room
1, 9:05, A
1, 9:13, B
];
inner join IntervalMatch (VisitTime) LOAD EnterTime, ExitTime Resident UserLogins;
Then you can create a table that reproduces your above reference. Probably this is not the final result, because you need to test and specify what to do with more visits per User etc (here you probably need to rework the calculation of ExitTime. And I think we need to handle complete timestamps, not only the hour:minute part).
Regards,
Stefan
.png "Luminary Alumni"){kind=icon}
