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

    Complicated Table Mapping Problem

      Hi I have two tables here.







      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.



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



      Xue Bin

        • Complicated Table Mapping Problem

          Anyone has any idea?

            • Complicated Table Mapping Problem

              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.



                    • Complicated Table Mapping Problem

                      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:)



                      Xue Bin

                        • Re: Complicated Table Mapping Problem
                          Stefan Wühl

                          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:



                          LOAD * INLINE [

                          UserName,    UserId,    EnterTime

                          a,    1,    9:00

                          b,    1,    9:10




                          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;



                          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).