8 Replies Latest reply: May 23, 2012 11:18 AM by Bin Xue RSS

Complicated Table Mapping Problem

Bin Xue

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

  • Complicated Table Mapping Problem
    Bin Xue

    Anyone has any idea?

    • Complicated Table Mapping Problem
      Bin Xue

      Shall I assume it is quite impossible with Qlikview?

      • Complicated Table Mapping Problem
        Jason Michaelides

        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
          Bin Xue

          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

            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