topic Complicated Table Mapping Problem in QlikView

Complicated Table Mapping Problem

Tue, 22 May 2012 02:44:06 GMT

Hi I have two tables here.

UserName	UserId	EnterTime
a	1	9:00
b	1	9:10

UserId	VisitTime	Room
1	9:05	A
1	9:13	B

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.

UserName	UserId	EnterTime	VisitTime	Room
a	1	9:00	9:05	A
b	1	9:10	9:13	B

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

Regards,

Xue Bin

Complicated Table Mapping Problem

Tue, 22 May 2012 03:40:01 GMT

Anyone has any idea?

Complicated Table Mapping Problem

Wed, 23 May 2012 07:13:54 GMT

Shall I assume it is quite impossible with Qlikview?

Complicated Table Mapping Problem

Jason_Michaelides — Wed, 23 May 2012 07:56:55 GMT

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

Complicated Table Mapping Problem

Wed, 23 May 2012 08:12:24 GMT

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

Re: Complicated Table Mapping Problem

swuehl — Wed, 23 May 2012 12:26:47 GMT

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

Complicated Table Mapping Problem

Jason_Michaelides — Wed, 23 May 2012 12:46:24 GMT

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

Jason

Complicated Table Mapping Problem

Wed, 23 May 2012 15:16:26 GMT

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

Complicated Table Mapping Problem

Wed, 23 May 2012 15:18:13 GMT

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

Regards,

Xue Bin