Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Complicated Table Mapping Problem

Hi I have two tables here.

UserNameUserIdEnterTime
a19:00
b19:10

UserIdVisitTimeRoom
19:05A
19:13B

Basically user a checks in at 9:00. He gets a pass with a userId 1. He then visits room A at 9:05. He left immediately. Then user b checks in at 9:10. He got the same pass as user a as security passes are reused. He then visits Room B at 9:13. I need the output table as below.

UserNameUserIdEnterTimeVisitTimeRoom
a19:009:05A
b19:109:13B


Is there a way to accomplish this in Qlikview? Thanks in advance.

Regards,

Xue Bin

8 Replies
Not applicable
Author

Anyone has any idea?

Not applicable
Author

Shall I assume it is quite impossible with Qlikview?

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Not impossible at all.  Sometimes it can take a few hours to get a response on the forum as people can get busy!  I'm off to a meeting now but will look at this later for you if no-one else has picked it up in the meantime.

Jason

Not applicable
Author

Thank you Jason. I think it must be a difficult problem. Normally I would get a reply very soon. But it's been a day and only you have responded:)

Regards,

Xue Bin

swuehl
MVP
MVP

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Thanks Stefan - I've been rather hung up this morning and not able to get to this.  I'll leave it to you!

Jason

Not applicable
Author

Hi Stefan,

I was really anxious to get this problem solved as this is a client requirement and got a bit impatient. Your comment came just in time. I was not aware of interval match previously. I will read more about it tomorrow and try to change my script. Many thanks to you:)

Regards,

Xue Bin

Not applicable
Author

Thanks Jason, really appreciate that you still took time to reply out of your busy schedule:)

Regards,

Xue Bin